create sequence Z_TEST_SEQ
	maxvalue 999999999
/

create sequence DATA_VERSION
	maxvalue 99999999999
/

create sequence OVERSEA_PACKAGE_LIST_BOX_SEQ
	maxvalue 999999999
/

create sequence OVERSEA_PACKAGE_LIST_DTL_SEQ
	maxvalue 999999999
/

create sequence OVERSEA_OPERATE_LOG_SEQ
	maxvalue 99999999999
/

create sequence ITEM_AUTO_ID_SEQ
	maxvalue 99999999999
/

create sequence OVERSEA_REQUISITION_SEQ
	maxvalue 999999999
/

create sequence OVERSEA_REQUISITION_DTL_SEQ
	maxvalue 999999999
/

create sequence OVERSEA_WHS_OUT_SEQ
	maxvalue 999999999
/

create sequence OVERSEA_WHS_OUT_DTL_SEQ
	maxvalue 999999999
/

create sequence BOX_NOTE_REPACKAGE
	nocache
/

create sequence OVERSEA_DELIVERY_NOTE_DTL_SEQ
	maxvalue 999999999
/

create sequence OVERSEA_WHS_DUMP_DTL_SEQ
	nocache
/

create sequence OVERSEA_DN_DTL_SEQ
	nocache
/

create sequence OVERSEA_PURCHASE_ORDER_DTL_SEQ
	nocache
/

create table MATERIAL_SAP_WERKS_RELATION
(
	MATERIAL_NO VARCHAR2(32) default NULL,
	WERKS VARCHAR2(6),
	IS_DELETE VARCHAR2(2),
	MATERIAL_STATUS VARCHAR2(2)
)
/

comment on table MATERIAL_SAP_WERKS_RELATION is '物料工厂关系表'
/

comment on column MATERIAL_SAP_WERKS_RELATION.MATERIAL_NO is '物料编码'
/

comment on column MATERIAL_SAP_WERKS_RELATION.WERKS is '工厂代码'
/

comment on column MATERIAL_SAP_WERKS_RELATION.IS_DELETE is '删除标识（工厂层级）'
/

comment on column MATERIAL_SAP_WERKS_RELATION.MATERIAL_STATUS is '物料状态（工厂层级）'
/

create trigger TR_MATERIAL_SAP_WERKS_RELATION
	before insert
	on MATERIAL_SAP_WERKS_RELATION
	for each row
DECLARE
    NUM NUMBER := 0;
BEGIN
    SELECT COUNT(*) INTO NUM FROM MATERIAL_SAP_WERKS_RELATION MS
    WHERE MS.MATERIAL_NO = :NEW.MATERIAL_NO AND MS.WERKS = :NEW.WERKS;
    if NUM = 1 THEN
        DELETE FROM MATERIAL_SAP_WERKS_RELATION M
        WHERE M.MATERIAL_NO = :NEW.MATERIAL_NO AND M.WERKS = :NEW.WERKS ;
    end if;
end;
/

create table WERKS
(
	WERKS VARCHAR2(6) not null
		primary key,
	WERKS_NAME_ZH VARCHAR2(128),
	WERKS_NAME_EN VARCHAR2(128),
	COMPANY_CODE VARCHAR2(6),
	COMPANY_NAME_ZH VARCHAR2(128),
	COMPANY_NAME_EN VARCHAR2(128),
	REGIO_GROUP VARCHAR2(10),
	COUNTRY VARCHAR2(64),
	CREATE_TIME DATE default TO_DATE(TO_CHAR(SYS_EXTRACT_UTC(SYSTIMESTAMP), 'yyyy-mm-dd hh24:mi:ss'), 'yyyy-mm-dd hh24:mi:ss'),
	SUPPLIER_NO VARCHAR2(32)
)
/

comment on table WERKS is '工厂信息表'
/

comment on column WERKS.WERKS is '工厂代码'
/

comment on column WERKS.WERKS_NAME_ZH is '工厂中文名称'
/

comment on column WERKS.WERKS_NAME_EN is '工厂英文名称'
/

comment on column WERKS.COMPANY_CODE is '公司代码'
/

comment on column WERKS.COMPANY_NAME_ZH is '公司中文名称'
/

comment on column WERKS.COMPANY_NAME_EN is '公司英文名称'
/

comment on column WERKS.REGIO_GROUP is '工厂分类（10保税工厂，20贸易工厂，30生产工厂，40虚拟销售工厂）'
/

comment on column WERKS.COUNTRY is '国家'
/

comment on column WERKS.SUPPLIER_NO is '供应商编号'
/

create table WHS
(
	WERKS VARCHAR2(6),
	WHS_LOCATION_CODE VARCHAR2(6),
	WHS_LOCATION_DESC VARCHAR2(128),
	WHS_TYPE VARCHAR2(32),
	CONTACT VARCHAR2(64),
	ADDRESS VARCHAR2(255),
	TELEPHONE VARCHAR2(50),
	CITY VARCHAR2(64),
	CREATE_TIME DATE default TO_DATE(TO_CHAR(SYS_EXTRACT_UTC(SYSTIMESTAMP), 'yyyy-mm-dd hh24:mi:ss'), 'yyyy-mm-dd hh24:mi:ss')
)
/

comment on table WHS is '仓库信息表'
/

comment on column WHS.WERKS is '工厂代码（和仓库地点代码一起确定唯一性）'
/

comment on column WHS.WHS_LOCATION_CODE is '仓储地点代码（和工厂代码一起确定唯一性）'
/

comment on column WHS.WHS_LOCATION_DESC is '仓库地点描述'
/

comment on column WHS.WHS_TYPE is '仓储类型-自定义字段（贸易仓、备件仓、复合仓）'
/

comment on column WHS.CONTACT is '联系人'
/

comment on column WHS.ADDRESS is '地址'
/

comment on column WHS.TELEPHONE is '电话'
/

comment on column WHS.CITY is '城市'
/

create index WHS_WERKS_LOCATION_CODE
	on WHS (WERKS, WHS_LOCATION_CODE)
/

create table SPMS_USER
(
	WORK_CODE VARCHAR2(60) not null
		primary key,
	WORK_ID VARCHAR2(20),
	LAST_NAME VARCHAR2(60),
	TELEPHONE VARCHAR2(60),
	MOBILE VARCHAR2(60),
	EMAIL VARCHAR2(60),
	POSITION_ID VARCHAR2(30),
	POSITION_NAME VARCHAR2(128),
	DEPT_ID VARCHAR2(30),
	DEPT_NAME VARCHAR2(128),
	SUBCOMPANY_ID VARCHAR2(20),
	SUBCOMPANY_NAME VARCHAR2(128),
	COMPANY VARCHAR2(100),
	BRANCH_POINT VARCHAR2(100),
	BRANCH_LINE VARCHAR2(100),
	MANAGER_ID VARCHAR2(20),
	MANAGER_NAME VARCHAR2(60),
	OA_STATUS VARCHAR2(20),
	LOGIN_PASSWORD VARCHAR2(255),
	SPMS_STATUS VARCHAR2(2) default 1
)
/

comment on table SPMS_USER is 'SPMS用户信息表'
/

comment on column SPMS_USER.WORK_CODE is '员工编号'
/

comment on column SPMS_USER.WORK_ID is '员工ID-员工的OAID'
/

comment on column SPMS_USER.LAST_NAME is '员工姓名'
/

comment on column SPMS_USER.TELEPHONE is '电话'
/

comment on column SPMS_USER.MOBILE is '手机'
/

comment on column SPMS_USER.EMAIL is '邮箱'
/

comment on column SPMS_USER.POSITION_ID is '岗位ID'
/

comment on column SPMS_USER.POSITION_NAME is '岗位名称'
/

comment on column SPMS_USER.DEPT_ID is '部门ID'
/

comment on column SPMS_USER.DEPT_NAME is '部门名称'
/

comment on column SPMS_USER.SUBCOMPANY_ID is '分部ID'
/

comment on column SPMS_USER.SUBCOMPANY_NAME is '分部名称'
/

comment on column SPMS_USER.COMPANY is '分公司'
/

comment on column SPMS_USER.BRANCH_POINT is '分点'
/

comment on column SPMS_USER.BRANCH_LINE is '分线'
/

comment on column SPMS_USER.MANAGER_ID is '直接上级ID'
/

comment on column SPMS_USER.MANAGER_NAME is '直接上级名称'
/

comment on column SPMS_USER.OA_STATUS is '状态-0：试用 1：正式 2：临时 3：试用延期'
/

comment on column SPMS_USER.LOGIN_PASSWORD is '登陆密码'
/

comment on column SPMS_USER.SPMS_STATUS is '员工在SPMS系统状态-默认1正常；0停用；'
/

create table MATERIAL_SRM_LONG_CONTRACT
(
	MATERIAL_NO VARCHAR2(32) not null,
	LONG_CONTRACT_CODE VARCHAR2(128) not null
		constraint MATERIAL_SRM_LONG_CONTRACT_LONG_CONTRACT_CODE_PK
			primary key,
	SUPPLIER_CODE VARCHAR2(32),
	SUPPLIER_NAME VARCHAR2(128),
	START_DATE VARCHAR2(32),
	END_DATE VARCHAR2(32),
	WERKS VARCHAR2(32),
	PROCUREMENT_ORGANIZATION VARCHAR2(32),
	TAX_PRICE NUMBER(18,6),
	QUANTITY VARCHAR2(32),
	UNIT VARCHAR2(32),
	CURRENCY_CODE VARCHAR2(32),
	MATERIAL_PIC_LINK VARCHAR2(32),
	TAX_CODE VARCHAR2(32),
	TAX_RATE VARCHAR2(32),
	PURCHASING_CYCLE VARCHAR2(128),
	MIN_PACK_QUANTITY VARCHAR2(32),
	MIN_ORDER_QUANTITY VARCHAR2(32),
	BRAND VARCHAR2(32),
	SOURCE_GOODS VARCHAR2(32),
	SPMS_STATUS VARCHAR2(32)
)
/

comment on table MATERIAL_SRM_LONG_CONTRACT is 'SRM长协表'
/

comment on column MATERIAL_SRM_LONG_CONTRACT.MATERIAL_NO is '物料编号'
/

comment on column MATERIAL_SRM_LONG_CONTRACT.LONG_CONTRACT_CODE is '长协编号'
/

comment on column MATERIAL_SRM_LONG_CONTRACT.SUPPLIER_CODE is '供应商编号'
/

comment on column MATERIAL_SRM_LONG_CONTRACT.SUPPLIER_NAME is '供应商名称'
/

comment on column MATERIAL_SRM_LONG_CONTRACT.START_DATE is '有效期起始日'
/

comment on column MATERIAL_SRM_LONG_CONTRACT.END_DATE is '有效期截止日'
/

comment on column MATERIAL_SRM_LONG_CONTRACT.WERKS is '工厂'
/

comment on column MATERIAL_SRM_LONG_CONTRACT.PROCUREMENT_ORGANIZATION is '采购组织'
/

comment on column MATERIAL_SRM_LONG_CONTRACT.TAX_PRICE is '含税价'
/

comment on column MATERIAL_SRM_LONG_CONTRACT.QUANTITY is '数量'
/

comment on column MATERIAL_SRM_LONG_CONTRACT.UNIT is '单位'
/

comment on column MATERIAL_SRM_LONG_CONTRACT.CURRENCY_CODE is '币种'
/

comment on column MATERIAL_SRM_LONG_CONTRACT.MATERIAL_PIC_LINK is '链接地址'
/

comment on column MATERIAL_SRM_LONG_CONTRACT.TAX_CODE is '税码'
/

comment on column MATERIAL_SRM_LONG_CONTRACT.TAX_RATE is '税率'
/

comment on column MATERIAL_SRM_LONG_CONTRACT.PURCHASING_CYCLE is '采购周期'
/

comment on column MATERIAL_SRM_LONG_CONTRACT.MIN_PACK_QUANTITY is '最小包装数量'
/

comment on column MATERIAL_SRM_LONG_CONTRACT.MIN_ORDER_QUANTITY is '最小订单数量'
/

comment on column MATERIAL_SRM_LONG_CONTRACT.BRAND is '品牌'
/

comment on column MATERIAL_SRM_LONG_CONTRACT.SOURCE_GOODS is '货源地'
/

comment on column MATERIAL_SRM_LONG_CONTRACT.SPMS_STATUS is 'spms状态'
/

create table ORDER_WERKS
(
	SPMS_ID VARCHAR2(64) not null
		primary key,
	URGENCY VARCHAR2(32),
	APPLICATION_DATE VARCHAR2(64),
	DATE_REQUIRED VARCHAR2(128),
	SUBJECT VARCHAR2(128),
	STATUS VARCHAR2(64),
	WORK_CODE VARCHAR2(64),
	LAST_NAME VARCHAR2(64),
	WERKS VARCHAR2(128),
	WORKSHOP_CODE VARCHAR2(128),
	DEPT_ID VARCHAR2(128),
	POSITION_ID VARCHAR2(128),
	COMPANY VARCHAR2(128),
	BRANCH_POINT VARCHAR2(128),
	BRANCH_LINE VARCHAR2(128),
	PURCHASE_LOCATION VARCHAR2(32),
	JUSTIFICATION VARCHAR2(1024),
	PROJECT_CODE VARCHAR2(64),
	ESTIMATE_COST NUMBER(15,3),
	PROJECT_TYPE VARCHAR2(128),
	PROJECT_STATUS VARCHAR2(128),
	PROJECT_CATEGORY VARCHAR2(128),
	SHIPPING_TYPE VARCHAR2(32),
	SHIPPING_FANGSHI VARCHAR2(32),
	APPLICATION_TYPE VARCHAR2(128),
	DEVICE_CATEGORY VARCHAR2(128),
	DEVICE_TYPE VARCHAR2(32),
	MATERIAL_CATEGORY VARCHAR2(128),
	APPROVAL_COMMENTS VARCHAR2(1024),
	OA_ID VARCHAR2(64),
	OA_PROCESS_ID VARCHAR2(64),
	OA_APPROVAL_DATE VARCHAR2(64),
	FIELD_FOR_OA VARCHAR2(128),
	DETAIL_EXPLAIN VARCHAR2(1024),
	RELEVANT_PROCESS VARCHAR2(1024),
	RELEVANT_DOCUMENT VARCHAR2(1024),
	PURCHASE_MANAGER VARCHAR2(128),
	COST_DEPT_ID VARCHAR2(128),
	COST_DEPT_NAME VARCHAR2(128),
	DEVICE_STATEMENT VARCHAR2(1024),
	DEVICE_ATTACHMENT VARCHAR2(1024),
	COMMENTS VARCHAR2(1024),
	OA_BIANHAO VARCHAR2(128),
	RELEVANT_DEPARTMENT VARCHAR2(128),
	ATTACHMENT_LIST VARCHAR2(1024),
	CREATE_TIME DATE default TO_DATE(TO_CHAR(SYS_EXTRACT_UTC(SYSTIMESTAMP), 'yyyy-mm-dd hh24:mi:ss'), 'yyyy-mm-dd hh24:mi:ss'),
	UUID VARCHAR2(32)
)
/

comment on table ORDER_WERKS is '工厂请购单'
/

comment on column ORDER_WERKS.SPMS_ID is '工厂请购单号'
/

comment on column ORDER_WERKS.URGENCY is '紧急程度，0正常；1重要；2紧急'
/

comment on column ORDER_WERKS.APPLICATION_DATE is '申请日期'
/

comment on column ORDER_WERKS.DATE_REQUIRED is '要求到货日期'
/

comment on column ORDER_WERKS.SUBJECT is '标题'
/

comment on column ORDER_WERKS.STATUS is '请购单SPMS状态-0关闭；1机电保存；2机电提交OA成功；3OA审批通过；4OA审批不通过；5提交SRM；6机电提交OA失败；7重新提交OA；8提交SRM失败；'
/

comment on column ORDER_WERKS.WORK_CODE is '工号'
/

comment on column ORDER_WERKS.LAST_NAME is '申请人'
/

comment on column ORDER_WERKS.WERKS is '工厂代码'
/

comment on column ORDER_WERKS.WORKSHOP_CODE is '车间代码'
/

comment on column ORDER_WERKS.DEPT_ID is '部门'
/

comment on column ORDER_WERKS.POSITION_ID is '岗位'
/

comment on column ORDER_WERKS.COMPANY is '分公司'
/

comment on column ORDER_WERKS.BRANCH_POINT is '分点'
/

comment on column ORDER_WERKS.BRANCH_LINE is '分线'
/

comment on column ORDER_WERKS.PURCHASE_LOCATION is '采购地点,1国内/中国，2海外/其他'
/

comment on column ORDER_WERKS.JUSTIFICATION is '申请原因'
/

comment on column ORDER_WERKS.PROJECT_CODE is '项目号'
/

comment on column ORDER_WERKS.ESTIMATE_COST is '预计费用合计'
/

comment on column ORDER_WERKS.PROJECT_TYPE is '项目类型,0-洗衣粉厂 1-五金加工厂 2-磁砖厂 3-肯尼亚砖厂 4-加纳砖厂 5-坦桑砖厂 6-科特五金厂 7-坦桑制钉厂 8-塞内加尔项目组 9-纸尿裤厂 10-加纳后山项目 11-分公司贸易 12-莫桑比克砖厂 13-赞比亚陶瓷厂'
/

comment on column ORDER_WERKS.PROJECT_STATUS is '项目状态，0已投产；1未投产'
/

comment on column ORDER_WERKS.PROJECT_CATEGORY is '项目类别'
/

comment on column ORDER_WERKS.SHIPPING_TYPE is '是否海运,0是，1否'
/

comment on column ORDER_WERKS.SHIPPING_FANGSHI is '运输方式, 0海运；1空运；2人带'
/

comment on column ORDER_WERKS.APPLICATION_TYPE is '申请类别，0临时采购，1月度；2季度；3年度'
/

comment on column ORDER_WERKS.DEVICE_CATEGORY is '设备/物料类别，3备品备件；4行政物资；5工厂设备；6筹建物资'
/

comment on column ORDER_WERKS.DEVICE_TYPE is '设备类型,0实验室设备；生产设备'
/

comment on column ORDER_WERKS.MATERIAL_CATEGORY is '物料类别'
/

comment on column ORDER_WERKS.APPROVAL_COMMENTS is '签字意见'
/

comment on column ORDER_WERKS.OA_ID is '员工OA编号'
/

comment on column ORDER_WERKS.OA_PROCESS_ID is 'OA工作流ID：0703，1201，3601'
/

comment on column ORDER_WERKS.OA_APPROVAL_DATE is '请购单OA审批通过日期'
/

comment on column ORDER_WERKS.FIELD_FOR_OA is '内部订单号'
/

comment on column ORDER_WERKS.DETAIL_EXPLAIN is '详细情况说明'
/

comment on column ORDER_WERKS.RELEVANT_PROCESS is '相关流程'
/

comment on column ORDER_WERKS.RELEVANT_DOCUMENT is '相关附件'
/

comment on column ORDER_WERKS.PURCHASE_MANAGER is '采购经理'
/

comment on column ORDER_WERKS.COST_DEPT_ID is '费用承担部门ID'
/

comment on column ORDER_WERKS.COST_DEPT_NAME is '费用承担部门NAME'
/

comment on column ORDER_WERKS.DEVICE_STATEMENT is '设备清单说明'
/

comment on column ORDER_WERKS.DEVICE_ATTACHMENT is '设备清单附件'
/

comment on column ORDER_WERKS.COMMENTS is '备注说明'
/

comment on column ORDER_WERKS.OA_BIANHAO is '编号'
/

comment on column ORDER_WERKS.RELEVANT_DEPARTMENT is '工厂关联部门'
/

comment on column ORDER_WERKS.ATTACHMENT_LIST is '附件清单'
/

comment on column ORDER_WERKS.CREATE_TIME is '单据创建系统时间戳'
/

comment on column ORDER_WERKS.UUID is 'java生成唯一ID，用于关联工厂请购单明细表'
/

create trigger TR_ORDER_WERKS_BEF
	before insert
	on ORDER_WERKS
	for each row
DECLARE
    V_NUM1 NUMBER := 0;
  BEGIN
    SELECT NVL(TO_NUMBER(SUBSTR(MAX(OW.SPMS_ID), 11, 13)), 0) INTO V_NUM1 FROM ORDER_WERKS OW
    WHERE SUBSTR(OW.APPLICATION_DATE, 0, 4) = SUBSTR(:NEW.APPLICATION_DATE, 0, 4)
          AND OW.WERKS = :NEW.WERKS;
    :NEW.SPMS_ID := :NEW.WERKS||'-'||SUBSTR(:NEW.APPLICATION_DATE, 0, 4)|| '-'|| SUBSTR(1001 + V_NUM1, 2, 4);
  END;
/

create table ORDER_WERKS_DTL
(
	SPMS_ID VARCHAR2(64),
	ROW_ID_WERKS NUMBER,
	SPMS_WS_ID VARCHAR2(64),
	ROW_ID_WS NUMBER,
	UUID VARCHAR2(32)
)
/

comment on table ORDER_WERKS_DTL is '工厂请购单详情关联表'
/

comment on column ORDER_WERKS_DTL.SPMS_ID is '工厂请购单号'
/

comment on column ORDER_WERKS_DTL.ROW_ID_WERKS is '工厂请购单行号'
/

comment on column ORDER_WERKS_DTL.SPMS_WS_ID is '车间请购单号'
/

comment on column ORDER_WERKS_DTL.ROW_ID_WS is '车间请购单行号'
/

comment on column ORDER_WERKS_DTL.UUID is 'java生成唯一ID，用于关联工厂请购单表。新增数据时关联获取工厂请购单编号。'
/

create trigger TR_ORDER_WERKS_DTL_BEF
	before insert
	on ORDER_WERKS_DTL
	for each row
DECLARE
    WS_ID VARCHAR2(64) := 0;
  BEGIN
    SELECT OW.SPMS_ID INTO WS_ID FROM ORDER_WERKS OW WHERE OW.UUID = :NEW.UUID;
    :NEW.SPMS_ID := WS_ID;
  END;
/

create table SHOPPING_CART
(
	WORK_CODE VARCHAR2(32) not null,
	MATERIAL_NO VARCHAR2(64) not null,
	QUANTITY NUMBER,
	CREATE_TIME DATE default TO_DATE(TO_CHAR(SYS_EXTRACT_UTC(SYSTIMESTAMP), 'yyyy-mm-dd hh24:mi:ss'), 'yyyy-mm-dd hh24:mi:ss'),
	UPDATE_TIME DATE default NULL,
	LONG_CONTRACT_CODE VARCHAR2(128),
	BRAND VARCHAR2(40)
)
/

comment on table SHOPPING_CART is '购物车表'
/

comment on column SHOPPING_CART.WORK_CODE is '创建者编号-用户工号'
/

comment on column SHOPPING_CART.MATERIAL_NO is '物料编号'
/

comment on column SHOPPING_CART.QUANTITY is '数量'
/

comment on column SHOPPING_CART.CREATE_TIME is '创建时间'
/

comment on column SHOPPING_CART.UPDATE_TIME is '更新时间'
/

comment on column SHOPPING_CART.LONG_CONTRACT_CODE is '长协编号'
/

comment on column SHOPPING_CART.BRAND is '品牌'
/

create trigger TR_SHOPPING_CART_BEF
	before update
	on SHOPPING_CART
	for each row
BEGIN
:new.UPDATE_TIME := TO_DATE(TO_CHAR(SYS_EXTRACT_UTC(SYSTIMESTAMP), 'yyyy-mm-dd hh24:mi:ss'), 'yyyy-mm-dd hh24:mi:ss');
END;
/

create table PURCHASE_PATTERN
(
	PATTERN_ID VARCHAR2(64) not null
		primary key,
	PATTERN_NAME VARCHAR2(256),
	WERKS VARCHAR2(64),
	PATTERN_DESC VARCHAR2(1024),
	IS_DELETED VARCHAR2(4),
	MACHINE_ID VARCHAR2(64),
	REMARK VARCHAR2(512),
	UPDATE_DATE DATE default null,
	CREATE_DATE DATE default TO_DATE(TO_CHAR(SYS_EXTRACT_UTC(SYSTIMESTAMP), 'yyyy-mm-dd hh24:mi:ss'), 'yyyy-mm-dd hh24:mi:ss')
)
/

comment on table PURCHASE_PATTERN is '请购场景表'
/

comment on column PURCHASE_PATTERN.PATTERN_ID is '场景编号'
/

comment on column PURCHASE_PATTERN.PATTERN_NAME is '请购场景名称'
/

comment on column PURCHASE_PATTERN.WERKS is '工厂编号'
/

comment on column PURCHASE_PATTERN.PATTERN_DESC is '请购场景描述'
/

comment on column PURCHASE_PATTERN.IS_DELETED is '是否删除，1是，0否，默认0'
/

comment on column PURCHASE_PATTERN.MACHINE_ID is '关联设备编号'
/

comment on column PURCHASE_PATTERN.REMARK is '备注信息'
/

comment on column PURCHASE_PATTERN.UPDATE_DATE is '单据更新系统时间戳'
/

comment on column PURCHASE_PATTERN.CREATE_DATE is '单据创建系统时间戳'
/

create trigger TR_PURCHASE_PATTERN_BEF
	before update
	on PURCHASE_PATTERN
	for each row
BEGIN
:new.UPDATE_DATE := TO_DATE(TO_CHAR(SYS_EXTRACT_UTC(SYSTIMESTAMP), 'yyyy-mm-dd hh24:mi:ss'), 'yyyy-mm-dd hh24:mi:ss');
END;
/

create table PURCHASE_PATTERN_DTL
(
	PATTERN_ID VARCHAR2(64),
	ROW_ID NUMBER,
	MATERIAL_NO VARCHAR2(128),
	MATERIAL_ZH_DESC VARCHAR2(256),
	MATERIAL_EN_DESC VARCHAR2(256),
	REQUEST_QTY NUMBER,
	BASIC_UNIT VARCHAR2(128),
	BRAND VARCHAR2(128),
	ROW_STATUS VARCHAR2(32),
	ROW_COMMENT VARCHAR2(1024),
	DEVICE_ID VARCHAR2(128),
	DEVICE_CATEGORY VARCHAR2(128),
	DEVICE_TYPE VARCHAR2(32),
	MATERIAL_CATEGORY VARCHAR2(128)
)
/

comment on table PURCHASE_PATTERN_DTL is '场景请购单详情表'
/

comment on column PURCHASE_PATTERN_DTL.PATTERN_ID is '场景请购单号'
/

comment on column PURCHASE_PATTERN_DTL.ROW_ID is '场景请购单行号'
/

comment on column PURCHASE_PATTERN_DTL.MATERIAL_NO is '物料编号'
/

comment on column PURCHASE_PATTERN_DTL.MATERIAL_ZH_DESC is '物料描述中文'
/

comment on column PURCHASE_PATTERN_DTL.MATERIAL_EN_DESC is '物料描述英文'
/

comment on column PURCHASE_PATTERN_DTL.REQUEST_QTY is '数量'
/

comment on column PURCHASE_PATTERN_DTL.BASIC_UNIT is '基本单位'
/

comment on column PURCHASE_PATTERN_DTL.BRAND is '品牌'
/

comment on column PURCHASE_PATTERN_DTL.ROW_STATUS is '行项目状态'
/

comment on column PURCHASE_PATTERN_DTL.ROW_COMMENT is '行项目备注'
/

comment on column PURCHASE_PATTERN_DTL.DEVICE_ID is '需求匹配的设备编码'
/

comment on column PURCHASE_PATTERN_DTL.DEVICE_CATEGORY is '设备/物料类别，3备品备件；4行政物资；5工厂设备；6筹建物资'
/

comment on column PURCHASE_PATTERN_DTL.DEVICE_TYPE is '设备类型,0实验室设备；生产设备'
/

comment on column PURCHASE_PATTERN_DTL.MATERIAL_CATEGORY is '物料类别'
/

create table WORKSHOP
(
	WERKS VARCHAR2(6),
	WORKSHOP_CODE VARCHAR2(16),
	WORKSHOP_NAME_ZH VARCHAR2(128),
	WORKSHOP_NAME_EN VARCHAR2(128),
	WORKSHOP_DESC VARCHAR2(255)
)
/

comment on table WORKSHOP is '车间信息表'
/

comment on column WORKSHOP.WERKS is '工厂代码'
/

comment on column WORKSHOP.WORKSHOP_CODE is '车间代码'
/

comment on column WORKSHOP.WORKSHOP_NAME_ZH is '车间中文名称'
/

comment on column WORKSHOP.WORKSHOP_NAME_EN is '车间英文名称'
/

comment on column WORKSHOP.WORKSHOP_DESC is '车间描述'
/

create table SPMS_USER_WHS
(
	WORK_CODE VARCHAR2(60),
	WERKS VARCHAR2(6),
	WHS_LOCATION_CODE VARCHAR2(6)
)
/

comment on table SPMS_USER_WHS is '用户仓库关联关系表'
/

comment on column SPMS_USER_WHS.WORK_CODE is '用户工号'
/

comment on column SPMS_USER_WHS.WERKS is '工厂代码'
/

comment on column SPMS_USER_WHS.WHS_LOCATION_CODE is '车间地点代码'
/

create table SPMS_USER_WORKSHOP
(
	WORK_CODE VARCHAR2(60),
	WERKS VARCHAR2(6),
	WORKSHOP_CODE VARCHAR2(16)
)
/

comment on table SPMS_USER_WORKSHOP is '用户车间关联关系表'
/

comment on column SPMS_USER_WORKSHOP.WORK_CODE is '用户工号'
/

comment on column SPMS_USER_WORKSHOP.WERKS is '工厂代码'
/

comment on column SPMS_USER_WORKSHOP.WORKSHOP_CODE is '车间代码'
/

create table WHS_INVENTORY_INFO
(
	WERKS VARCHAR2(6),
	WHS_LOCATION_CODE VARCHAR2(6),
	MATERIAL_NO VARCHAR2(32),
	MATERIAL_ZH_DESC VARCHAR2(255),
	MATERIAL_EN_DESC VARCHAR2(255),
	BASIC_UNIT VARCHAR2(32),
	QUANTITY NUMBER,
	SAFETY_QUANTITY NUMBER,
	UPDATE_TIME DATE,
	UPDATE_USER VARCHAR2(60),
	BOX_NOTE VARCHAR2(50),
	CREATE_TIME DATE default TO_DATE(TO_CHAR(SYS_EXTRACT_UTC(SYSTIMESTAMP), 'yyyy-mm-dd hh24:mi:ss'), 'yyyy-mm-dd hh24:mi:ss')
)
/

comment on table WHS_INVENTORY_INFO is '仓库库存信息表'
/

comment on column WHS_INVENTORY_INFO.WERKS is '工厂代码'
/

comment on column WHS_INVENTORY_INFO.WHS_LOCATION_CODE is '仓储地点代码'
/

comment on column WHS_INVENTORY_INFO.MATERIAL_NO is '物料编码'
/

comment on column WHS_INVENTORY_INFO.MATERIAL_ZH_DESC is '物料中文描述'
/

comment on column WHS_INVENTORY_INFO.MATERIAL_EN_DESC is '物料英文描述'
/

comment on column WHS_INVENTORY_INFO.BASIC_UNIT is '基本单位'
/

comment on column WHS_INVENTORY_INFO.QUANTITY is '仓库库存数量'
/

comment on column WHS_INVENTORY_INFO.SAFETY_QUANTITY is '安全库存数量'
/

comment on column WHS_INVENTORY_INFO.UPDATE_TIME is '更新时间'
/

comment on column WHS_INVENTORY_INFO.UPDATE_USER is '更新人'
/

comment on column WHS_INVENTORY_INFO.BOX_NOTE is '箱码-仅用于自管仓备件货管理'
/

comment on column WHS_INVENTORY_INFO.CREATE_TIME is '创建时间'
/

create trigger TR_WHS_INVENTORY_INFO_BEF
	before update
	on WHS_INVENTORY_INFO
	for each row
BEGIN
:new.UPDATE_TIME := TO_DATE(TO_CHAR(SYS_EXTRACT_UTC(SYSTIMESTAMP), 'yyyy-mm-dd hh24:mi:ss'), 'yyyy-mm-dd hh24:mi:ss');
END;
/

create table ORDER_WORKSHOP
(
	UUID VARCHAR2(32),
	SPMS_WS_ID VARCHAR2(64) not null
		primary key,
	URGENCY VARCHAR2(32),
	APPLICATION_DATE VARCHAR2(64),
	DATE_REQUIRED VARCHAR2(128),
	SUBJECT VARCHAR2(128),
	WORK_CODE VARCHAR2(64),
	LAST_NAME VARCHAR2(64),
	WERKS VARCHAR2(6),
	WORKSHOP_CODE VARCHAR2(128),
	JUSTIFICATION VARCHAR2(1024),
	PROJECT_CODE VARCHAR2(64),
	PROJECT_TYPE VARCHAR2(128),
	PROJECT_STATUS VARCHAR2(128),
	STATUS VARCHAR2(64),
	CREATE_DATE VARCHAR2(16),
	UPDATE_TIME DATE default TO_DATE(TO_CHAR(SYS_EXTRACT_UTC(SYSTIMESTAMP), 'yyyy-mm-dd hh24:mi:ss'), 'yyyy-mm-dd hh24:mi:ss'),
	CREATE_TIME DATE default TO_DATE(TO_CHAR(SYS_EXTRACT_UTC(SYSTIMESTAMP), 'yyyy-mm-dd hh24:mi:ss'), 'yyyy-mm-dd hh24:mi:ss'),
	PURCHASE_LOCATION VARCHAR2(32)
)
/

comment on table ORDER_WORKSHOP is '车间请购单'
/

comment on column ORDER_WORKSHOP.UUID is 'java生成唯一ID，用于关联请购单明细表'
/

comment on column ORDER_WORKSHOP.SPMS_WS_ID is '车间请购单号'
/

comment on column ORDER_WORKSHOP.URGENCY is '紧急程度'
/

comment on column ORDER_WORKSHOP.APPLICATION_DATE is '提交日期'
/

comment on column ORDER_WORKSHOP.DATE_REQUIRED is '要求到货日期'
/

comment on column ORDER_WORKSHOP.SUBJECT is '标题'
/

comment on column ORDER_WORKSHOP.WORK_CODE is '员工工号'
/

comment on column ORDER_WORKSHOP.LAST_NAME is '员工姓名'
/

comment on column ORDER_WORKSHOP.WERKS is '工厂代码，必输字段'
/

comment on column ORDER_WORKSHOP.WORKSHOP_CODE is '车间编号'
/

comment on column ORDER_WORKSHOP.JUSTIFICATION is '申请原因'
/

comment on column ORDER_WORKSHOP.PROJECT_CODE is '项目号'
/

comment on column ORDER_WORKSHOP.PROJECT_TYPE is '项目类型,0-洗衣粉厂 1-五金加工厂 2-磁砖厂 3-肯尼亚砖厂 4-加纳砖厂 5-坦桑砖厂 6-科特五金厂 7-坦桑制钉厂 8-塞内加尔项目组 9-纸尿裤厂 10-加纳后山项目 11-分公司贸易 12-莫桑比克砖厂 13-赞比亚陶瓷厂'
/

comment on column ORDER_WORKSHOP.PROJECT_STATUS is '项目状态，0已投产；1未投产'
/

comment on column ORDER_WORKSHOP.STATUS is '车间请购单SPMS状态'
/

comment on column ORDER_WORKSHOP.CREATE_DATE is '创建日期，前端传入格式:yyyy-mm-dd'
/

comment on column ORDER_WORKSHOP.UPDATE_TIME is '单据更新系统时间戳，自动生成'
/

comment on column ORDER_WORKSHOP.CREATE_TIME is '单据创建系统时间戳,自动生成'
/

comment on column ORDER_WORKSHOP.PURCHASE_LOCATION is '采购地点'
/

create trigger TR_ORDER_WORKSHOP_BEF
	before update
	on ORDER_WORKSHOP
	for each row
BEGIN
:new.UPDATE_TIME := TO_DATE(TO_CHAR(SYS_EXTRACT_UTC(SYSTIMESTAMP), 'yyyy-mm-dd hh24:mi:ss'), 'yyyy-mm-dd hh24:mi:ss');
END;
/

create trigger TR_ORDER_WORKSHOP_BEF1
	before insert
	on ORDER_WORKSHOP
	for each row
DECLARE
    V_NUM1 NUMBER := 0;
  BEGIN
    SELECT NVL(TO_NUMBER(SUBSTR(MAX(OW.SPMS_WS_ID), -3, 3)), 0) INTO V_NUM1 FROM ORDER_WORKSHOP OW
    WHERE OW.CREATE_DATE = :NEW.CREATE_DATE
          AND OW.WERKS = :NEW.WERKS
          AND OW.WORKSHOP_CODE = :NEW.WORKSHOP_CODE;
    :NEW.SPMS_WS_ID := :NEW.WERKS||'-'||:NEW.WORKSHOP_CODE||'-'||REPLACE(:NEW.CREATE_DATE, '-')||'-'||SUBSTR(1001 + V_NUM1, 2, 4);
  END;
/

create table ORDER_WORKSHOP_DTL
(
	UUID VARCHAR2(32),
	SPMS_WS_ID VARCHAR2(64),
	ROW_ID NUMBER,
	MATERIAL_NO VARCHAR2(128),
	MATERIAL_ZH_DESC VARCHAR2(256),
	MATERIAL_EN_DESC VARCHAR2(256),
	REQUEST_QTY NUMBER,
	APPROVAL_QTY NUMBER,
	BASIC_UNIT VARCHAR2(128),
	BRAND VARCHAR2(128),
	ROW_STATUS VARCHAR2(32),
	ROW_COMMENT VARCHAR2(1024),
	DEVICE_ID VARCHAR2(128),
	DEVICE_CATEGORY VARCHAR2(128),
	DEVICE_TYPE VARCHAR2(32),
	MATERIAL_CATEGORY VARCHAR2(128),
	LONG_CONTRACT_CODE VARCHAR2(128)
)
/

comment on table ORDER_WORKSHOP_DTL is '车间请购单详情表'
/

comment on column ORDER_WORKSHOP_DTL.UUID is 'java生成唯一ID，用于和车间请购单表关联。新增数据时关联获取车间请购单号。'
/

comment on column ORDER_WORKSHOP_DTL.SPMS_WS_ID is '车间请购单号'
/

comment on column ORDER_WORKSHOP_DTL.ROW_ID is '车间请购单行号'
/

comment on column ORDER_WORKSHOP_DTL.MATERIAL_NO is '物料编号'
/

comment on column ORDER_WORKSHOP_DTL.MATERIAL_ZH_DESC is '物料描述中文'
/

comment on column ORDER_WORKSHOP_DTL.MATERIAL_EN_DESC is '物料描述英文'
/

comment on column ORDER_WORKSHOP_DTL.REQUEST_QTY is '提交数量（车间主任）'
/

comment on column ORDER_WORKSHOP_DTL.APPROVAL_QTY is '批准数量（机电主任）'
/

comment on column ORDER_WORKSHOP_DTL.BASIC_UNIT is '基本单位'
/

comment on column ORDER_WORKSHOP_DTL.BRAND is '品牌'
/

comment on column ORDER_WORKSHOP_DTL.ROW_STATUS is '行项目状态'
/

comment on column ORDER_WORKSHOP_DTL.ROW_COMMENT is '行项目备注'
/

comment on column ORDER_WORKSHOP_DTL.DEVICE_ID is '需求匹配的设备编码'
/

comment on column ORDER_WORKSHOP_DTL.DEVICE_CATEGORY is '设备/物料类别，3备品备件；4行政物资；5工厂设备；6筹建物资'
/

comment on column ORDER_WORKSHOP_DTL.DEVICE_TYPE is '设备类型,0实验室设备；生产设备'
/

comment on column ORDER_WORKSHOP_DTL.MATERIAL_CATEGORY is '物料类别'
/

comment on column ORDER_WORKSHOP_DTL.LONG_CONTRACT_CODE is '长协编号'
/

create trigger TR_ORDER_WORKSHOP_DTL_BEF
	before insert
	on ORDER_WORKSHOP_DTL
	for each row
DECLARE
    WS_ID VARCHAR2(64) := 0;
    BEGIN
        SELECT OW.SPMS_WS_ID INTO WS_ID FROM ORDER_WORKSHOP OW WHERE OW.UUID = :NEW.UUID;
        :NEW.SPMS_WS_ID := WS_ID;
END;
/

create table ORDER_LOG
(
	WORK_CODE VARCHAR2(64),
	COMMENTS VARCHAR2(1024),
	CREATE_TIME DATE default TO_DATE(TO_CHAR(SYS_EXTRACT_UTC(SYSTIMESTAMP), 'yyyy-mm-dd hh24:mi:ss'), 'yyyy-mm-dd hh24:mi:ss'),
	UUID VARCHAR2(32),
	LOG_AUTO_ID NUMBER
)
/

comment on table ORDER_LOG is '订单审批记录表'
/

comment on column ORDER_LOG.WORK_CODE is '用户工号'
/

comment on column ORDER_LOG.COMMENTS is '审批意见/操作内容'
/

comment on column ORDER_LOG.CREATE_TIME is '创建时间'
/

comment on column ORDER_LOG.UUID is 'UUID关联，用于关联（车间/工厂）请购单表UUID获取请购单号'
/

comment on column ORDER_LOG.LOG_AUTO_ID is '日志信息自增ID'
/

create index INDEX_LOG_AUTO_ID
	on ORDER_LOG (LOG_AUTO_ID)
/

create trigger TR_ORDER_LOG_BEF
	before insert
	on ORDER_LOG
	for each row
DECLARE
    V_NUM1 NUMBER := 0;
BEGIN
    SELECT NVL(TO_NUMBER(MAX(LOG.LOG_AUTO_ID)), 0) INTO V_NUM1 FROM ORDER_LOG LOG;
        :NEW.LOG_AUTO_ID := V_NUM1 + 1;
END;
/

create table SYS_LOG
(
	UUID VARCHAR2(32) default null not null
		constraint SYS_LOG_PK
			primary key,
	LOG_TYPE VARCHAR2(2),
	LOG_CONTENT VARCHAR2(1000),
	OPERATE_TYPE VARCHAR2(64),
	USER_ID VARCHAR2(40),
	USERNAME VARCHAR2(128),
	IP VARCHAR2(100),
	METHOD VARCHAR2(255),
	REQUEST_URL VARCHAR2(255),
	REQUEST_TYPE VARCHAR2(10),
	COST_TIME NUMBER,
	CREATE_TIME DATE default TO_DATE(TO_CHAR(SYS_EXTRACT_UTC(SYSTIMESTAMP), 'yyyy-mm-dd hh24:mi:ss'), 'yyyy-mm-dd hh24:mi:ss'),
	LOG_AUTO_ID NUMBER,
	RELATE_DOC_NUMBER VARCHAR2(64),
	REQUEST_PARAM CLOB,
	REMARKS CLOB,
	RETURNED_MESSAGE CLOB
)
/

comment on table SYS_LOG is '系统日志表'
/

comment on column SYS_LOG.UUID is '唯一ID'
/

comment on column SYS_LOG.LOG_TYPE is '日志类型（1操作日志,2登录日志,3提交OA记录,4请求SAP日志）'
/

comment on column SYS_LOG.LOG_CONTENT is '日志内容'
/

comment on column SYS_LOG.OPERATE_TYPE is '操作类型-登录；查询；添加；修改；删除；导入；导出；'
/

comment on column SYS_LOG.USER_ID is '用户工号'
/

comment on column SYS_LOG.USERNAME is '用户名'
/

comment on column SYS_LOG.IP is 'IP地址'
/

comment on column SYS_LOG.METHOD is '请求方法'
/

comment on column SYS_LOG.REQUEST_URL is '请求路径'
/

comment on column SYS_LOG.REQUEST_TYPE is '请求类型'
/

comment on column SYS_LOG.COST_TIME is '耗时(ms)'
/

comment on column SYS_LOG.CREATE_TIME is '创建时间'
/

comment on column SYS_LOG.LOG_AUTO_ID is '日志表自增ID'
/

comment on column SYS_LOG.RELATE_DOC_NUMBER is '关联单据号'
/

comment on column SYS_LOG.REQUEST_PARAM is '请求参数'
/

comment on column SYS_LOG.REMARKS is '备注信息'
/

comment on column SYS_LOG.RETURNED_MESSAGE is '返回参数'
/

create index INDEX_SYS_LOG_AUTO_ID
	on SYS_LOG (LOG_AUTO_ID)
/

create trigger TR_SYS_LOG_BEF
	before insert
	on SYS_LOG
	for each row
DECLARE
    V_NUM1 NUMBER := 0;
BEGIN
    SELECT NVL(TO_NUMBER(MAX(LOG.LOG_AUTO_ID)), 0) INTO V_NUM1 FROM SYS_LOG LOG;
    :NEW.LOG_AUTO_ID := V_NUM1 + 1;
END;
/

create table SHOPPING_SCENARIO
(
	UUID VARCHAR2(32) not null
		constraint SHOPPING_SCENARIO_PK
			primary key,
	SCENARIO_ID VARCHAR2(16),
	WERKS VARCHAR2(6),
	SCENARIO_NAME VARCHAR2(256),
	SCENARIO_DESC VARCHAR2(256),
	MACHINE_NO VARCHAR2(64),
	MACHINE_DESC VARCHAR2(128),
	REMARKS VARCHAR2(128),
	IS_DELETE VARCHAR2(2) default 0,
	CREATE_USERID VARCHAR2(64),
	CREATE_TIME DATE default TO_DATE(TO_CHAR(SYS_EXTRACT_UTC(SYSTIMESTAMP), 'yyyy-mm-dd hh24:mi:ss'), 'yyyy-mm-dd hh24:mi:ss'),
	UPDATE_USERID VARCHAR2(64),
	UPDATE_TIME DATE
)
/

comment on table SHOPPING_SCENARIO is '请购场景表表头'
/

comment on column SHOPPING_SCENARIO.UUID is '请购场景表唯一主键UUID'
/

comment on column SHOPPING_SCENARIO.SCENARIO_ID is '请购场景编号-工厂代码+四位序列号'
/

comment on column SHOPPING_SCENARIO.WERKS is '工厂代码，必输字段'
/

comment on column SHOPPING_SCENARIO.SCENARIO_NAME is '场景名称'
/

comment on column SHOPPING_SCENARIO.SCENARIO_DESC is '场景描述'
/

comment on column SHOPPING_SCENARIO.MACHINE_NO is '关联设备编号'
/

comment on column SHOPPING_SCENARIO.MACHINE_DESC is '关联设备描述信息'
/

comment on column SHOPPING_SCENARIO.REMARKS is '备注说明'
/

comment on column SHOPPING_SCENARIO.IS_DELETE is '是否删除-默认0否，1是'
/

comment on column SHOPPING_SCENARIO.CREATE_USERID is '创建人工号'
/

comment on column SHOPPING_SCENARIO.CREATE_TIME is '创建时间-默认当前国际时间'
/

comment on column SHOPPING_SCENARIO.UPDATE_USERID is '更新人工号'
/

create trigger TR_SHOPPING_SCENARIO_BEF
	before update
	on SHOPPING_SCENARIO
	for each row
BEGIN
    :new.UPDATE_TIME := TO_DATE(TO_CHAR(SYS_EXTRACT_UTC(SYSTIMESTAMP), 'yyyy-mm-dd hh24:mi:ss'), 'yyyy-mm-dd hh24:mi:ss');
END;
/

create trigger TR_SHOPPING_SCENARIO_BEF1
	before insert
	on SHOPPING_SCENARIO
	for each row
DECLARE
    V_NUM1 NUMBER := 0;
BEGIN
    SELECT NVL(TO_NUMBER(SUBSTR(MAX(SS.SCENARIO_ID), -3, 3)), 0) INTO V_NUM1 FROM SHOPPING_SCENARIO SS
    WHERE SS.WERKS = :NEW.WERKS;
    :NEW.SCENARIO_ID := :NEW.WERKS||'-'||SUBSTR(1001 + V_NUM1, 2, 4);
END;
/

create table SHOPPING_SCENARIO_DTL
(
	UUID VARCHAR2(32),
	SSD_ROW_ID NUMBER,
	MATERIAL_NO VARCHAR2(64),
	QUANTITY NUMBER,
	LONG_CONTRACT_CODE VARCHAR2(128),
	BRAND VARCHAR2(40)
)
/

comment on table SHOPPING_SCENARIO_DTL is '请购场景表明细'
/

comment on column SHOPPING_SCENARIO_DTL.UUID is '表头UUID，唯一关联字段'
/

comment on column SHOPPING_SCENARIO_DTL.SSD_ROW_ID is '行编号'
/

comment on column SHOPPING_SCENARIO_DTL.MATERIAL_NO is '物料编号'
/

comment on column SHOPPING_SCENARIO_DTL.QUANTITY is '数量'
/

comment on column SHOPPING_SCENARIO_DTL.LONG_CONTRACT_CODE is '长协编号'
/

comment on column SHOPPING_SCENARIO_DTL.BRAND is '品牌'
/

create table ORDER_KEY_VALUE_CONFIG
(
	UUID VARCHAR2(32) not null
		constraint ORDER_KEY_VALUE_CONFIG_PK
			primary key,
	FIELD_KEY VARCHAR2(32),
	FIELD_NAME VARCHAR2(64),
	KEY_CODE VARCHAR2(32),
	KEY_CODE_DESC VARCHAR2(64),
	KEY_CODE_EN_DESC VARCHAR2(64),
	ENABLED VARCHAR2(8),
	SORT_NO NUMBER,
	REMARK VARCHAR2(256)
)
/

comment on table ORDER_KEY_VALUE_CONFIG is '订单请购码值配置信息表'
/

comment on column ORDER_KEY_VALUE_CONFIG.UUID is '唯一UUID'
/

comment on column ORDER_KEY_VALUE_CONFIG.FIELD_KEY is '码值对照字段'
/

comment on column ORDER_KEY_VALUE_CONFIG.FIELD_NAME is '码值对照字段名称'
/

comment on column ORDER_KEY_VALUE_CONFIG.KEY_CODE is '码值对照代码'
/

comment on column ORDER_KEY_VALUE_CONFIG.KEY_CODE_DESC is '码值对照代码描述'
/

comment on column ORDER_KEY_VALUE_CONFIG.KEY_CODE_EN_DESC is '码值对照代码英文描述'
/

comment on column ORDER_KEY_VALUE_CONFIG.ENABLED is '是否启用（1启用，默认1；0停用；）'
/

comment on column ORDER_KEY_VALUE_CONFIG.SORT_NO is '排序号'
/

comment on column ORDER_KEY_VALUE_CONFIG.REMARK is '备注信息'
/

create table SPMS_ROLE
(
	UUID VARCHAR2(32) not null
		primary key,
	ROLE_ID VARCHAR2(32) not null,
	ROLE_CN_NAME VARCHAR2(128),
	ROLE_EN_NAME VARCHAR2(128),
	ROLE_INFO VARCHAR2(255),
	CREATE_USER VARCHAR2(16),
	CREATE_TIME DATE default TO_DATE(TO_CHAR(SYS_EXTRACT_UTC(SYSTIMESTAMP), 'yyyy-mm-dd hh24:mi:ss'),
                                      'yyyy-mm-dd hh24:mi:ss'),
	UPDATE_USER VARCHAR2(16),
	UPDATE_TIME DATE
)
/

comment on column SPMS_ROLE.UUID is '唯一UUID'
/

comment on column SPMS_ROLE.ROLE_ID is '角色编号-主键'
/

comment on column SPMS_ROLE.ROLE_CN_NAME is '角色中文描述'
/

comment on column SPMS_ROLE.ROLE_EN_NAME is '角色英文描述'
/

comment on column SPMS_ROLE.ROLE_INFO is '角色信息'
/

comment on column SPMS_ROLE.CREATE_USER is '创建人工号'
/

comment on column SPMS_ROLE.CREATE_TIME is '创建时间'
/

comment on column SPMS_ROLE.UPDATE_USER is '更新人工号'
/

comment on column SPMS_ROLE.UPDATE_TIME is '更新时间'
/

create trigger TR_SPMS_ROLE_BEF
	before update
	on SPMS_ROLE
	for each row
BEGIN
:new.UPDATE_TIME := TO_DATE(TO_CHAR(SYS_EXTRACT_UTC(SYSTIMESTAMP), 'yyyy-mm-dd hh24:mi:ss'), 'yyyy-mm-dd hh24:mi:ss');
END;
/

create table SPMS_PERMISSION
(
	PERMISSION_ID VARCHAR2(32) not null
		primary key,
	PARENT_ID VARCHAR2(128),
	MENU_NAME VARCHAR2(128),
	MENU_EN_NAME VARCHAR2(128),
	URL VARCHAR2(255),
	COMPONENT_URL VARCHAR2(255),
	COMPONENT_NAME VARCHAR2(255),
	REDIRECT VARCHAR2(255),
	MENU_TYPE NUMBER,
	PERMS VARCHAR2(255),
	PERMS_TYPE VARCHAR2(32) default 0,
	SORT_NO NUMBER,
	ALWAYS_SHOW NUMBER,
	ICON VARCHAR2(128),
	IS_ROUTE NUMBER default 1,
	IS_LEAF NUMBER,
	DESCRIPTION VARCHAR2(255),
	CREATE_TIME DATE default TO_DATE(TO_CHAR(SYS_EXTRACT_UTC(SYSTIMESTAMP), 'yyyy-mm-dd hh24:mi:ss'),
                                                   'yyyy-mm-dd hh24:mi:ss'),
	CREATE_USER VARCHAR2(40),
	UPDATE_TIME DATE default NULL,
	UPDATE_USER VARCHAR2(40)
)
/

comment on table SPMS_PERMISSION is 'spms权限表'
/

comment on column SPMS_PERMISSION.PERMISSION_ID is '权限ID'
/

comment on column SPMS_PERMISSION.PARENT_ID is '父组件'
/

comment on column SPMS_PERMISSION.MENU_NAME is '菜单标题'
/

comment on column SPMS_PERMISSION.MENU_EN_NAME is '菜单英文标题'
/

comment on column SPMS_PERMISSION.URL is '路径'
/

comment on column SPMS_PERMISSION.COMPONENT_URL is '组件地址'
/

comment on column SPMS_PERMISSION.COMPONENT_NAME is '组件名称'
/

comment on column SPMS_PERMISSION.REDIRECT is '一级菜单跳转地址'
/

comment on column SPMS_PERMISSION.MENU_TYPE is '菜单类型（0：一级菜单；1：二级子菜单；3：三级子菜单；4：按钮权限；）'
/

comment on column SPMS_PERMISSION.PERMS is '菜单权限编码'
/

comment on column SPMS_PERMISSION.PERMS_TYPE is '权限策略 1：显示；2禁用；'
/

comment on column SPMS_PERMISSION.SORT_NO is '菜单排序'
/

comment on column SPMS_PERMISSION.ALWAYS_SHOW is '聚合子路由：1是，0否'
/

comment on column SPMS_PERMISSION.ICON is '菜单图标'
/

comment on column SPMS_PERMISSION.IS_ROUTE is '是否路由菜单：0不是，默认1是'
/

comment on column SPMS_PERMISSION.IS_LEAF is '是否叶子节点：1是，0不是'
/

comment on column SPMS_PERMISSION.DESCRIPTION is '描述'
/

comment on column SPMS_PERMISSION.CREATE_TIME is '创建时间'
/

comment on column SPMS_PERMISSION.CREATE_USER is '创建人工号'
/

comment on column SPMS_PERMISSION.UPDATE_TIME is '更新时间'
/

comment on column SPMS_PERMISSION.UPDATE_USER is '更新人工号'
/

create trigger TR_SPMS_PERMISSION_BEF
	before update
	on SPMS_PERMISSION
	for each row
BEGIN
:new.UPDATE_TIME := TO_DATE(TO_CHAR(SYS_EXTRACT_UTC(SYSTIMESTAMP), 'yyyy-mm-dd hh24:mi:ss'), 'yyyy-mm-dd hh24:mi:ss');
END;
/

create table SPMS_ROLE_PERMISSION
(
	UUID VARCHAR2(32) not null
		primary key,
	ROLE_UUID VARCHAR2(32),
	PERMISSION_ID VARCHAR2(32)
)
/

comment on table SPMS_ROLE_PERMISSION is 'spms角色权限关系表'
/

comment on column SPMS_ROLE_PERMISSION.UUID is '唯一UUID'
/

comment on column SPMS_ROLE_PERMISSION.ROLE_UUID is '角色表uuid'
/

comment on column SPMS_ROLE_PERMISSION.PERMISSION_ID is '权限编号'
/

create table SPMS_USER_ROLE
(
	UUID VARCHAR2(32) not null
		primary key,
	WORK_CODE VARCHAR2(60),
	ROLE_ID VARCHAR2(32),
	ROLE_UUID VARCHAR2(32)
)
/

comment on table SPMS_USER_ROLE is 'spms用户角色关系表'
/

comment on column SPMS_USER_ROLE.UUID is '唯一UUID'
/

comment on column SPMS_USER_ROLE.WORK_CODE is '用户工号'
/

comment on column SPMS_USER_ROLE.ROLE_ID is '角色编号'
/

comment on column SPMS_USER_ROLE.ROLE_UUID is '角色表UUID'
/

create table MATERIAL_STOCK_SAP
(
	WERKS VARCHAR2(6),
	WHS_LOCATION_CODE VARCHAR2(6),
	MATERIAL_NO VARCHAR2(32),
	QUANTITY NUMBER
)
/

comment on table MATERIAL_STOCK_SAP is '物料SAP库存表—库存同步'
/

comment on column MATERIAL_STOCK_SAP.WERKS is '工厂代码'
/

comment on column MATERIAL_STOCK_SAP.WHS_LOCATION_CODE is '仓库-仓库地点代码'
/

comment on column MATERIAL_STOCK_SAP.MATERIAL_NO is '物料编号'
/

comment on column MATERIAL_STOCK_SAP.QUANTITY is '数量-基本单位'
/

create table WHS_STORAGE
(
	UUID VARCHAR2(32) not null
		constraint WHS_STORAGE_PK
			primary key,
	WERKS VARCHAR2(6),
	WHS_LOCATION_CODE VARCHAR2(6),
	STORAGE_AREA VARCHAR2(32),
	SHELF_NO VARCHAR2(32),
	SHELF_LAYER VARCHAR2(32),
	SHELF_CELL VARCHAR2(32),
	STORAGE_NO VARCHAR2(32),
	STORAGE_TYPE VARCHAR2(2),
	STORAGE_INFO VARCHAR2(255),
	STORAGE_STATUS VARCHAR2(2) default 1,
	REMARKS VARCHAR2(255)
)
/

comment on table WHS_STORAGE is '仓库储位信息表'
/

comment on column WHS_STORAGE.UUID is '储位UUID'
/

comment on column WHS_STORAGE.WERKS is '工厂代码'
/

comment on column WHS_STORAGE.WHS_LOCATION_CODE is '仓库地点代码'
/

comment on column WHS_STORAGE.STORAGE_AREA is '库区'
/

comment on column WHS_STORAGE.SHELF_NO is '货架架子编号'
/

comment on column WHS_STORAGE.SHELF_LAYER is '货架架子层数编号'
/

comment on column WHS_STORAGE.SHELF_CELL is '货架层上格子编号'
/

comment on column WHS_STORAGE.STORAGE_NO is '储位编号（架号+层号+格子号）'
/

comment on column WHS_STORAGE.STORAGE_TYPE is '储位类型'
/

comment on column WHS_STORAGE.STORAGE_INFO is '储位信息说明'
/

comment on column WHS_STORAGE.STORAGE_STATUS is '储位状态（默认1启用，0停用）'
/

comment on column WHS_STORAGE.REMARKS is '备注说明'
/

create table WHS_STORAGE_INVENTORY
(
	UUID VARCHAR2(32),
	MATERIAL_NO VARCHAR2(32),
	BOX_NOTE VARCHAR2(50),
	QUANTITY NUMBER(13,3),
	AVAILABLE_QTY NUMBER(13,3),
	CREATE_TIME DATE default TO_DATE(TO_CHAR(SYS_EXTRACT_UTC(SYSTIMESTAMP), 'yyyy-mm-dd hh24:mi:ss'), 'yyyy-mm-dd hh24:mi:ss'),
	UPDATE_TIME DATE default TO_DATE(TO_CHAR(SYS_EXTRACT_UTC(SYSTIMESTAMP), 'yyyy-mm-dd hh24:mi:ss'), 'yyyy-mm-dd hh24:mi:ss'),
	BASIC_UNIT VARCHAR2(32),
	OPERATE_TIME VARCHAR2(32)
)
/

comment on table WHS_STORAGE_INVENTORY is '储位库存信息表'
/

comment on column WHS_STORAGE_INVENTORY.UUID is '储位UUID'
/

comment on column WHS_STORAGE_INVENTORY.MATERIAL_NO is '物料编码'
/

comment on column WHS_STORAGE_INVENTORY.BOX_NOTE is '箱码-仅用于自管仓备件货按箱处理使用'
/

comment on column WHS_STORAGE_INVENTORY.QUANTITY is '储位该物料总数量'
/

comment on column WHS_STORAGE_INVENTORY.AVAILABLE_QTY is '储位该物料可用总数量'
/

comment on column WHS_STORAGE_INVENTORY.CREATE_TIME is '创建时间'
/

comment on column WHS_STORAGE_INVENTORY.UPDATE_TIME is '更新时间'
/

comment on column WHS_STORAGE_INVENTORY.BASIC_UNIT is '基本单位'
/

comment on column WHS_STORAGE_INVENTORY.OPERATE_TIME is '操作时间'
/

create trigger TR_WHS_STORAGE_INVENTORY_BEF
	before update
	on WHS_STORAGE_INVENTORY
	for each row
BEGIN
:new.UPDATE_TIME := TO_DATE(TO_CHAR(SYS_EXTRACT_UTC(SYSTIMESTAMP), 'yyyy-mm-dd hh24:mi:ss'), 'yyyy-mm-dd hh24:mi:ss');
END;
/

create table SAP_DELIVERY_NOTE
(
	DELIVERY_NOTE_SAP VARCHAR2(10) not null
		primary key,
	PLANNED_ARRIVAL_DATE VARCHAR2(10),
	DELIVERY_TYPE VARCHAR2(4),
	DELIVERY_NOTE_SRM VARCHAR2(50),
	CREATE_DATE_SAP VARCHAR2(10),
	SPMS_MARK VARCHAR2(2),
	SUPPLIER_CODE VARCHAR2(50),
	SUPPLIER_DESC VARCHAR2(255),
	SPMS_STATUS VARCHAR2(2),
	PACKAGE_NOTE VARCHAR2(50),
	REMARKS VARCHAR2(255),
	DOCUMENT_DATE VARCHAR2(10),
	POSTING_DATE VARCHAR2(10),
	MOBILE_TYPE VARCHAR2(3),
	USER_ID VARCHAR2(10),
	PDA_SN VARCHAR2(32),
	SAP_VOUCHER_NUMBER VARCHAR2(16),
	SAP_VOUCHER_YEAR VARCHAR2(4),
	CREATE_TIME DATE default TO_DATE(TO_CHAR(SYS_EXTRACT_UTC(SYSTIMESTAMP), 'yyyy-mm-dd hh24:mi:ss'), 'yyyy-mm-dd hh24:mi:ss'),
	UPDATE_TIME DATE default TO_DATE(TO_CHAR(SYS_EXTRACT_UTC(SYSTIMESTAMP), 'yyyy-mm-dd hh24:mi:ss'), 'yyyy-mm-dd hh24:mi:ss'),
	OPERATE_USER VARCHAR2(255),
	CANCEL_POSTING_DATE VARCHAR2(12),
	CANCEL_SAP_VOUCHER_NUMBER VARCHAR2(32),
	CANCEL_REMARK VARCHAR2(255)
)
/

comment on table SAP_DELIVERY_NOTE is 'SAP交货单表头'
/

comment on column SAP_DELIVERY_NOTE.DELIVERY_NOTE_SAP is 'SAP交货单号'
/

comment on column SAP_DELIVERY_NOTE.PLANNED_ARRIVAL_DATE is '计划到货时间'
/

comment on column SAP_DELIVERY_NOTE.DELIVERY_TYPE is '交货类型'
/

comment on column SAP_DELIVERY_NOTE.DELIVERY_NOTE_SRM is 'SRM送货单号'
/

comment on column SAP_DELIVERY_NOTE.CREATE_DATE_SAP is 'SAP创建日期'
/

comment on column SAP_DELIVERY_NOTE.SPMS_MARK is 'SPMS标识（SAP提供-X表示备件货，空表示贸易货）'
/

comment on column SAP_DELIVERY_NOTE.SUPPLIER_CODE is '供应商编号'
/

comment on column SAP_DELIVERY_NOTE.SUPPLIER_DESC is '供应商描述'
/

comment on column SAP_DELIVERY_NOTE.SPMS_STATUS is 'SPMS 状态，任务开始执行则会被锁定，忽略更新'
/

comment on column SAP_DELIVERY_NOTE.PACKAGE_NOTE is '包装单号-有值为备件货；无值为贸易货；'
/

comment on column SAP_DELIVERY_NOTE.REMARKS is '交货单表头备注信息'
/

comment on column SAP_DELIVERY_NOTE.DOCUMENT_DATE is '凭证日期-开始处理日期'
/

comment on column SAP_DELIVERY_NOTE.POSTING_DATE is '过账日期-提交SAP日期'
/

comment on column SAP_DELIVERY_NOTE.MOBILE_TYPE is '移动类型'
/

comment on column SAP_DELIVERY_NOTE.USER_ID is '操作员工号/确认到货操作人员'
/

comment on column SAP_DELIVERY_NOTE.PDA_SN is 'PDA设备编号(验收时传入)'
/

comment on column SAP_DELIVERY_NOTE.SAP_VOUCHER_NUMBER is 'SAP物料凭证-提交SAP成功后回写'
/

comment on column SAP_DELIVERY_NOTE.SAP_VOUCHER_YEAR is '会计年份/SAP凭证年份'
/

comment on column SAP_DELIVERY_NOTE.CREATE_TIME is 'SPMS创建时间'
/

comment on column SAP_DELIVERY_NOTE.UPDATE_TIME is 'SPMS更新时间'
/

comment on column SAP_DELIVERY_NOTE.OPERATE_USER is '操作员/现场搬运工计费'
/

comment on column SAP_DELIVERY_NOTE.CANCEL_POSTING_DATE is '撤销SAP过账日期'
/

comment on column SAP_DELIVERY_NOTE.CANCEL_SAP_VOUCHER_NUMBER is '撤销SAP物料凭证-撤销提交SAP成功后回写'
/

comment on column SAP_DELIVERY_NOTE.CANCEL_REMARK is '撤销备注信息'
/

create trigger TR_SAP_DELIVERY_NOTE_BEF
	before update
	on SAP_DELIVERY_NOTE
	for each row
BEGIN
    :new.UPDATE_TIME := TO_DATE(TO_CHAR(SYS_EXTRACT_UTC(SYSTIMESTAMP), 'yyyy-mm-dd hh24:mi:ss'), 'yyyy-mm-dd hh24:mi:ss');
END;
/

create table SAP_DELIVERY_NOTE_DTL
(
	DELIVERY_NOTE_SAP VARCHAR2(10),
	DELIVERY_NOTE_SAP_ITEM NUMBER,
	PURCHASE_GROUP VARCHAR2(6),
	PURCHASE_GROUP_NAME VARCHAR2(64),
	MATERIAL_NO VARCHAR2(32),
	DELIVERY_QTY NUMBER(13,3),
	DELIVERY_UNIT VARCHAR2(3),
	WERKS VARCHAR2(6),
	WHS_LOCATION_CODE VARCHAR2(4),
	RECEIVED_QTY NUMBER(13,3),
	ITEM_STATUS VARCHAR2(2) default 0,
	OVER_DELIVERY_LIMIT NUMBER,
	UNDER_DELIVERY_LIMIT NUMBER,
	UNLIMITED VARCHAR2(2),
	REMARKS VARCHAR2(255),
	TEMP_STORAGE VARCHAR2(4000),
	WERKS_XQ VARCHAR2(6),
	QTY_SALES NUMBER(13,3),
	UNIT_SALES VARCHAR2(3),
	ACTUAL_QTY_SALES NUMBER(13,3),
	CREATE_TIME DATE default TO_DATE(TO_CHAR(SYS_EXTRACT_UTC(SYSTIMESTAMP), 'yyyy-mm-dd hh24:mi:ss'), 'yyyy-mm-dd hh24:mi:ss'),
	UPDATE_TIME DATE default TO_DATE(TO_CHAR(SYS_EXTRACT_UTC(SYSTIMESTAMP), 'yyyy-mm-dd hh24:mi:ss'), 'yyyy-mm-dd hh24:mi:ss'),
	SPMS_ITEM NUMBER,
	DOCUMENT_DATE VARCHAR2(10),
	SAP_VOUCHER_NUMBER VARCHAR2(16),
	SAP_VOUCHER_YEAR VARCHAR2(4),
	POSTING_DATE VARCHAR2(10),
	CANCEL_VOUCHER_NUMBER VARCHAR2(16),
	CANCEL_VOUCHER_YEAR VARCHAR2(4),
	CANCEL_REMARK VARCHAR2(255),
	USER_ID VARCHAR2(16),
	OPERATOR VARCHAR2(64)
)
/

comment on table SAP_DELIVERY_NOTE_DTL is 'SAP交货单表明细'
/

comment on column SAP_DELIVERY_NOTE_DTL.DELIVERY_NOTE_SAP is 'SAP交货单号'
/

comment on column SAP_DELIVERY_NOTE_DTL.DELIVERY_NOTE_SAP_ITEM is 'SAP交货单行号'
/

comment on column SAP_DELIVERY_NOTE_DTL.PURCHASE_GROUP is '采购组'
/

comment on column SAP_DELIVERY_NOTE_DTL.PURCHASE_GROUP_NAME is '采购组名称'
/

comment on column SAP_DELIVERY_NOTE_DTL.MATERIAL_NO is '物料编号'
/

comment on column SAP_DELIVERY_NOTE_DTL.DELIVERY_QTY is '交货数量'
/

comment on column SAP_DELIVERY_NOTE_DTL.DELIVERY_UNIT is '交货单位-基本单位'
/

comment on column SAP_DELIVERY_NOTE_DTL.WERKS is '工厂'
/

comment on column SAP_DELIVERY_NOTE_DTL.WHS_LOCATION_CODE is '库存地点'
/

comment on column SAP_DELIVERY_NOTE_DTL.RECEIVED_QTY is '实际收货数量（基本单位）'
/

comment on column SAP_DELIVERY_NOTE_DTL.ITEM_STATUS is '交货单贸易货行项目状态（默认0-待到货；3-收货中；7-提交SAP失败；8-提交SAP完成；11-删除/关闭；12-已撤销；）'
/

comment on column SAP_DELIVERY_NOTE_DTL.OVER_DELIVERY_LIMIT is '过量交货限度'
/

comment on column SAP_DELIVERY_NOTE_DTL.UNDER_DELIVERY_LIMIT is '交货不足限度'
/

comment on column SAP_DELIVERY_NOTE_DTL.UNLIMITED is '无限制'
/

comment on column SAP_DELIVERY_NOTE_DTL.REMARKS is '交货单行项目备注'
/

comment on column SAP_DELIVERY_NOTE_DTL.TEMP_STORAGE is '物料临时储位信息表，包含：物料-储位-数量关系'
/

comment on column SAP_DELIVERY_NOTE_DTL.WERKS_XQ is '需求工厂'
/

comment on column SAP_DELIVERY_NOTE_DTL.QTY_SALES is '销售单位应收数量'
/

comment on column SAP_DELIVERY_NOTE_DTL.UNIT_SALES is '销售单位'
/

comment on column SAP_DELIVERY_NOTE_DTL.ACTUAL_QTY_SALES is '销售单位实收数量'
/

comment on column SAP_DELIVERY_NOTE_DTL.CREATE_TIME is 'SPMS创建时间'
/

comment on column SAP_DELIVERY_NOTE_DTL.UPDATE_TIME is 'SPMS更新时间'
/

comment on column SAP_DELIVERY_NOTE_DTL.SPMS_ITEM is '交货单明细SPMS行号'
/

comment on column SAP_DELIVERY_NOTE_DTL.DOCUMENT_DATE is '凭证日期-开始处理日期'
/

comment on column SAP_DELIVERY_NOTE_DTL.SAP_VOUCHER_NUMBER is 'SAP凭证号'
/

comment on column SAP_DELIVERY_NOTE_DTL.SAP_VOUCHER_YEAR is 'SAP凭证年份'
/

comment on column SAP_DELIVERY_NOTE_DTL.POSTING_DATE is '过账日期'
/

comment on column SAP_DELIVERY_NOTE_DTL.CANCEL_VOUCHER_NUMBER is '撤销凭证号'
/

comment on column SAP_DELIVERY_NOTE_DTL.CANCEL_VOUCHER_YEAR is '撤销凭证年份'
/

comment on column SAP_DELIVERY_NOTE_DTL.CANCEL_REMARK is '撤销备注'
/

comment on column SAP_DELIVERY_NOTE_DTL.USER_ID is '用户工号'
/

comment on column SAP_DELIVERY_NOTE_DTL.OPERATOR is '操作员'
/

create trigger TR_SAP_DELIVERY_NOTE_DTL_BEF_UPDATE
	before update
	on SAP_DELIVERY_NOTE_DTL
	for each row
BEGIN
    :new.UPDATE_TIME := TO_DATE(TO_CHAR(SYS_EXTRACT_UTC(SYSTIMESTAMP), 'yyyy-mm-dd hh24:mi:ss'), 'yyyy-mm-dd hh24:mi:ss');
END;
/

create trigger TR_SAP_DELIVERY_NOTE_DTL_BEF_INSERT
	before insert
	on SAP_DELIVERY_NOTE_DTL
	for each row
DECLARE
    V_NUM1 NUMBER := 0;
BEGIN
    IF (NVL(:NEW.SPMS_ITEM, 0) = 0)
    THEN
        SELECT NVL(MAX(SDND.SPMS_ITEM), 0) INTO V_NUM1 FROM SAP_DELIVERY_NOTE_DTL SDND WHERE SDND.DELIVERY_NOTE_SAP = :NEW.DELIVERY_NOTE_SAP;
        :NEW.SPMS_ITEM := V_NUM1 + 10;
    END IF;
END;
/

create table SAP_DELIVERY_NOTE_SPMS
(
	SPMS_UUID VARCHAR2(32) not null
		primary key,
	SPMS_ID VARCHAR2(32),
	TASK_STATUS VARCHAR2(2),
	DELIVERY_NOTE_SAP VARCHAR2(10) not null,
	PLANNED_ARRIVAL_DATE VARCHAR2(10),
	DELIVERY_TYPE VARCHAR2(4),
	DELIVERY_NOTE_SRM VARCHAR2(35),
	CREATE_DATE_SAP VARCHAR2(10),
	SPMS_MARK VARCHAR2(2),
	SUPPLIER_CODE VARCHAR2(10),
	SUPPLIER_DESC VARCHAR2(200),
	SPMS_STATUS VARCHAR2(2),
	WERKS VARCHAR2(6),
	WHS_LOCATION_CODE VARCHAR2(4),
	DOCUMENT_DATE VARCHAR2(10),
	POSTING_DATE VARCHAR2(10),
	OPERATE_USERNAME VARCHAR2(40),
	MOBILE_TYPE VARCHAR2(6),
	REMARKS VARCHAR2(128),
	CREATE_USER VARCHAR2(40),
	CREATE_TIME DATE default TO_DATE(TO_CHAR(SYS_EXTRACT_UTC(SYSTIMESTAMP), 'yyyy-mm-dd hh24:mi:ss'), 'yyyy-mm-dd hh24:mi:ss')
)
/

comment on table SAP_DELIVERY_NOTE_SPMS is 'SPMS交货单表头'
/

comment on column SAP_DELIVERY_NOTE_SPMS.SPMS_UUID is 'SPMS UUID'
/

comment on column SAP_DELIVERY_NOTE_SPMS.SPMS_ID is 'SPMS任务凭证号'
/

comment on column SAP_DELIVERY_NOTE_SPMS.TASK_STATUS is 'SPMS任务状态'
/

comment on column SAP_DELIVERY_NOTE_SPMS.DELIVERY_NOTE_SAP is 'SAP交货单号'
/

comment on column SAP_DELIVERY_NOTE_SPMS.PLANNED_ARRIVAL_DATE is '计划到货时间'
/

comment on column SAP_DELIVERY_NOTE_SPMS.DELIVERY_TYPE is '交货类型'
/

comment on column SAP_DELIVERY_NOTE_SPMS.DELIVERY_NOTE_SRM is 'SRM送货单号'
/

comment on column SAP_DELIVERY_NOTE_SPMS.CREATE_DATE_SAP is 'SAP创建日期'
/

comment on column SAP_DELIVERY_NOTE_SPMS.SPMS_MARK is 'SPMS标识（SAP提供-X表示备件货，空表示贸易货）'
/

comment on column SAP_DELIVERY_NOTE_SPMS.SUPPLIER_CODE is '供应商编号'
/

comment on column SAP_DELIVERY_NOTE_SPMS.SUPPLIER_DESC is '供应商描述'
/

comment on column SAP_DELIVERY_NOTE_SPMS.SPMS_STATUS is 'SPMS 状态，任务开始执行则会被锁定，忽略更新'
/

comment on column SAP_DELIVERY_NOTE_SPMS.WERKS is '工厂'
/

comment on column SAP_DELIVERY_NOTE_SPMS.WHS_LOCATION_CODE is '收货仓库'
/

comment on column SAP_DELIVERY_NOTE_SPMS.DOCUMENT_DATE is '凭证日期'
/

comment on column SAP_DELIVERY_NOTE_SPMS.POSTING_DATE is '过账日期'
/

comment on column SAP_DELIVERY_NOTE_SPMS.OPERATE_USERNAME is '操作人姓名'
/

comment on column SAP_DELIVERY_NOTE_SPMS.MOBILE_TYPE is '移动类型'
/

comment on column SAP_DELIVERY_NOTE_SPMS.REMARKS is '备注'
/

comment on column SAP_DELIVERY_NOTE_SPMS.CREATE_USER is '创建人'
/

comment on column SAP_DELIVERY_NOTE_SPMS.CREATE_TIME is '创建时间'
/

create table SAP_PACKAGE_NOTE
(
	PACKAGE_NOTE VARCHAR2(50) not null
		primary key,
	DELIVERY_NOTE_SRM VARCHAR2(50),
	DELIVERY_NOTE_SAP VARCHAR2(50),
	SUPPLIER_CODE VARCHAR2(32),
	TOTAL_VOLUME NUMBER(13,3),
	TOTAL_WEIGHT NUMBER(13,3),
	LOAD_BEGIN_TIME VARCHAR2(10),
	LOAD_END_TIME VARCHAR2(10),
	TRANSPORTER VARCHAR2(255),
	CONTRACTOR_INFO VARCHAR2(32),
	RECEIVER VARCHAR2(128),
	RECEIVER_INFO VARCHAR2(50),
	RECEIVING_ADDRESS VARCHAR2(255),
	SPMS_CONTRACT_NOTE VARCHAR2(64),
	SPMS_MARK VARCHAR2(2),
	CREATE_TIME DATE default TO_DATE(TO_CHAR(SYS_EXTRACT_UTC(SYSTIMESTAMP), 'yyyy-mm-dd hh24:mi:ss'), 'yyyy-mm-dd hh24:mi:ss')
)
/

comment on table SAP_PACKAGE_NOTE is '包装单号表'
/

comment on column SAP_PACKAGE_NOTE.PACKAGE_NOTE is '包装单号'
/

comment on column SAP_PACKAGE_NOTE.DELIVERY_NOTE_SRM is 'SRM送货单号'
/

comment on column SAP_PACKAGE_NOTE.DELIVERY_NOTE_SAP is 'SAP交货单号'
/

comment on column SAP_PACKAGE_NOTE.SUPPLIER_CODE is '供应商或债权人的帐号'
/

comment on column SAP_PACKAGE_NOTE.TOTAL_VOLUME is '总体积(CM3)'
/

comment on column SAP_PACKAGE_NOTE.TOTAL_WEIGHT is '总重量(KG)'
/

comment on column SAP_PACKAGE_NOTE.LOAD_BEGIN_TIME is '装卸开始时间'
/

comment on column SAP_PACKAGE_NOTE.LOAD_END_TIME is '装卸结束时间'
/

comment on column SAP_PACKAGE_NOTE.TRANSPORTER is '运输人'
/

comment on column SAP_PACKAGE_NOTE.CONTRACTOR_INFO is '承运人联系方式'
/

comment on column SAP_PACKAGE_NOTE.RECEIVER is '收货人'
/

comment on column SAP_PACKAGE_NOTE.RECEIVER_INFO is '收货人联系方式'
/

comment on column SAP_PACKAGE_NOTE.RECEIVING_ADDRESS is '收货地址'
/

comment on column SAP_PACKAGE_NOTE.SPMS_CONTRACT_NOTE is 'SPMS线下合同号'
/

comment on column SAP_PACKAGE_NOTE.SPMS_MARK is 'SPMS标识'
/

comment on column SAP_PACKAGE_NOTE.CREATE_TIME is 'SPMS创建时间'
/

create table SAP_BOX_NOTE
(
	PACKAGE_NOTE VARCHAR2(50),
	BOX_NOTE VARCHAR2(50) not null
		primary key,
	BOX_MATERIAL_NAME VARCHAR2(50),
	TOTAL_QTY NUMBER,
	BOX_GROSS_WEIGHT NUMBER(13,3),
	BOX_VOLUME NUMBER(13,3),
	BOX_LENGTH NUMBER(13,3),
	BOX_WIDTH NUMBER(13,3),
	BOX_HEIGHT NUMBER(13,3),
	BOX_COLOR VARCHAR2(32),
	BOX_QTY_RECEIVED NUMBER,
	ITEM_STATUS VARCHAR2(2) default 0,
	REMARKS VARCHAR2(255),
	CREATE_TIME DATE default TO_DATE(TO_CHAR(SYS_EXTRACT_UTC(SYSTIMESTAMP), 'yyyy-mm-dd hh24:mi:ss'), 'yyyy-mm-dd hh24:mi:ss'),
	ARRIVAL_DATE VARCHAR2(16) default TO_CHAR(SYSDATE, 'yyyy-mm-dd'),
	PARENT_BOX_NOTE VARCHAR2(255)
)
/

comment on table SAP_BOX_NOTE is '箱码信息表'
/

comment on column SAP_BOX_NOTE.PACKAGE_NOTE is '包装单号'
/

comment on column SAP_BOX_NOTE.BOX_NOTE is '箱码/包装码'
/

comment on column SAP_BOX_NOTE.BOX_MATERIAL_NAME is '外箱材质名称'
/

comment on column SAP_BOX_NOTE.TOTAL_QTY is '装箱明细总数'
/

comment on column SAP_BOX_NOTE.BOX_GROSS_WEIGHT is '箱毛重(KG)'
/

comment on column SAP_BOX_NOTE.BOX_VOLUME is '箱体积(CCM)'
/

comment on column SAP_BOX_NOTE.BOX_LENGTH is '长(CM)'
/

comment on column SAP_BOX_NOTE.BOX_WIDTH is '宽(CM)'
/

comment on column SAP_BOX_NOTE.BOX_HEIGHT is '高(CM)'
/

comment on column SAP_BOX_NOTE.BOX_COLOR is '颜色'
/

comment on column SAP_BOX_NOTE.BOX_QTY_RECEIVED is '箱码-实收箱数/备件货'
/

comment on column SAP_BOX_NOTE.ITEM_STATUS is '箱码表状态信息'
/

comment on column SAP_BOX_NOTE.REMARKS is '箱码备注信息'
/

comment on column SAP_BOX_NOTE.CREATE_TIME is 'SPMS创建时间'
/

comment on column SAP_BOX_NOTE.ARRIVAL_DATE is '国内仓到货日期-库龄，数据创建默认给系统日期'
/

comment on column SAP_BOX_NOTE.PARENT_BOX_NOTE is '父箱箱码(拆箱,子箱存父箱箱码)'
/

create trigger TR_SAP_BOX_NOTE
	before insert
	on SAP_BOX_NOTE
	for each row
DECLARE
    NUM NUMBER := 0;
BEGIN
    SELECT COUNT(*) INTO NUM FROM SAP_BOX_NOTE SBN WHERE SBN.BOX_NOTE = :NEW.BOX_NOTE;
    if NUM = 1 THEN
        DELETE FROM SAP_BOX_NOTE SBN WHERE SBN.BOX_NOTE = :NEW.BOX_NOTE;
    end if;
end;
/

create trigger TR_CREATE_TIME
	before update
	on SAP_BOX_NOTE
	for each row
BEGIN
    :new.CREATE_TIME := TO_DATE(TO_CHAR(SYS_EXTRACT_UTC(SYSTIMESTAMP), 'yyyy-mm-dd hh24:mi:ss'), 'yyyy-mm-dd hh24:mi:ss');
END;
/

create table SAP_BOX_NOTE_DTL
(
	PACKAGE_NOTE VARCHAR2(50),
	ITEM NUMBER,
	BOX_NOTE VARCHAR2(50),
	PACKAGE_POSNR NUMBER,
	MATERIAL_NO VARCHAR2(18),
	SPECS VARCHAR2(128) default '',
	QUANTITY NUMBER(13,3),
	BASIC_UNIT VARCHAR2(3),
	SRM_CONTRACT_NO VARCHAR2(64),
	PURCHASE_NO VARCHAR2(64),
	PURCHASE_NO_ITEM NUMBER,
	DELIVERY_NOTE_SAP VARCHAR2(16),
	DELIVERY_NOTE_SAP_ITEM NUMBER,
	DEMAND_NO VARCHAR2(32),
	DEMAND_NO_ITEM NUMBER,
	DEMANDER VARCHAR2(64),
	REMARK VARCHAR2(128),
	GROSS_WEIGHT NUMBER(13,3),
	NET_WEIGHT NUMBER(13,3),
	DOMESTIC_QTY_RECEIVABLE NUMBER(13,3),
	DOMESTIC_QTY_RECEIVED NUMBER(13,3),
	OVERSEA_QTY_RECEIVED NUMBER(13,3),
	CREATE_TIME DATE default TO_DATE(TO_CHAR(SYS_EXTRACT_UTC(SYSTIMESTAMP), 'yyyy-mm-dd hh24:mi:ss'), 'yyyy-mm-dd hh24:mi:ss')
)
/

comment on table SAP_BOX_NOTE_DTL is '箱码行项目表'
/

comment on column SAP_BOX_NOTE_DTL.PACKAGE_NOTE is '包装单号'
/

comment on column SAP_BOX_NOTE_DTL.ITEM is '序号'
/

comment on column SAP_BOX_NOTE_DTL.BOX_NOTE is '箱码/包装码'
/

comment on column SAP_BOX_NOTE_DTL.PACKAGE_POSNR is '包装号'
/

comment on column SAP_BOX_NOTE_DTL.MATERIAL_NO is '物料号'
/

comment on column SAP_BOX_NOTE_DTL.SPECS is '规格型号'
/

comment on column SAP_BOX_NOTE_DTL.QUANTITY is '数量'
/

comment on column SAP_BOX_NOTE_DTL.BASIC_UNIT is '基本计量单位'
/

comment on column SAP_BOX_NOTE_DTL.SRM_CONTRACT_NO is 'SRM合同号'
/

comment on column SAP_BOX_NOTE_DTL.PURCHASE_NO is '采购凭证号'
/

comment on column SAP_BOX_NOTE_DTL.PURCHASE_NO_ITEM is '采购凭证的项目编号'
/

comment on column SAP_BOX_NOTE_DTL.DELIVERY_NOTE_SAP is '交货单号'
/

comment on column SAP_BOX_NOTE_DTL.DELIVERY_NOTE_SAP_ITEM is '交货单行项目编号'
/

comment on column SAP_BOX_NOTE_DTL.DEMAND_NO is '需求来源号'
/

comment on column SAP_BOX_NOTE_DTL.DEMAND_NO_ITEM is '需求来源号行号'
/

comment on column SAP_BOX_NOTE_DTL.DEMANDER is '需求申请人'
/

comment on column SAP_BOX_NOTE_DTL.REMARK is '备注'
/

comment on column SAP_BOX_NOTE_DTL.GROSS_WEIGHT is '毛重'
/

comment on column SAP_BOX_NOTE_DTL.NET_WEIGHT is '净重'
/

comment on column SAP_BOX_NOTE_DTL.DOMESTIC_QTY_RECEIVABLE is '国内收货数量-国内收获时箱的数量'
/

comment on column SAP_BOX_NOTE_DTL.DOMESTIC_QTY_RECEIVED is '国内实际数量-国内收货时默认箱内对应物料数量，可能拆分'
/

comment on column SAP_BOX_NOTE_DTL.OVERSEA_QTY_RECEIVED is '海外收货数量'
/

comment on column SAP_BOX_NOTE_DTL.CREATE_TIME is 'SPMS创建时间'
/

create table COST_CENTER
(
	COST_CENTER_CODE VARCHAR2(16) not null
		constraint COST_CENTER_PK
			primary key,
	COMPANY_CODE VARCHAR2(8),
	COST_CENTER_NAME VARCHAR2(128)
)
/

comment on table COST_CENTER is '成本中心'
/

comment on column COST_CENTER.COST_CENTER_CODE is '成本中心'
/

comment on column COST_CENTER.COMPANY_CODE is '公司代码'
/

comment on column COST_CENTER.COST_CENTER_NAME is '成本中心名称'
/

create trigger TR_COST_CENTER_BEF
	before insert
	on COST_CENTER
	for each row
DECLARE
    NUM NUMBER := 0;
BEGIN
    SELECT COUNT(*) INTO NUM FROM COST_CENTER CC
    WHERE CC.COST_CENTER_CODE = :NEW.COST_CENTER_CODE;
    if NUM = 1 THEN
        DELETE FROM COST_CENTER CC1
        WHERE CC1.COST_CENTER_CODE = :NEW.COST_CENTER_CODE;
    end if;
end;
/

create table MATERIAL_PHOTOS
(
	MATERIAL_NO VARCHAR2(32) not null
		constraint MATERIAL_PHOTOS_PK
			primary key,
	PHOTOS VARCHAR2(200) not null
)
/

create table MATERIAL_SPMS
(
	CATEGORY_ID NUMBER,
	MATERIAL_NO VARCHAR2(32),
	UUID VARCHAR2(32) default sys_guid() not null
		primary key
)
/

comment on table MATERIAL_SPMS is '物料表spms信息'
/

comment on column MATERIAL_SPMS.CATEGORY_ID is '目录分类编号-三级目录编号'
/

comment on column MATERIAL_SPMS.MATERIAL_NO is '物料编号'
/

create index INDEX_MATERIAL_SPMS_CATEGORY_ID
	on MATERIAL_SPMS (CATEGORY_ID)
/

create table SAP_DELIVERY_NOTE_LOG
(
	UUID VARCHAR2(32) not null
		primary key,
	DOCUMENT_NUMBER VARCHAR2(255),
	USER_ID VARCHAR2(20),
	OPERATION_TYPE VARCHAR2(255),
	COMMENTS VARCHAR2(255),
	RELATE_DOCUMENT_NUMBER VARCHAR2(32),
	CREATE_TIME DATE default TO_DATE(TO_CHAR(SYS_EXTRACT_UTC(SYSTIMESTAMP), 'yyyy-mm-dd hh24:mi:ss'), 'yyyy-mm-dd hh24:mi:ss'),
	LOG_AUTO_ID NUMBER,
	REMARK CLOB,
	REQUEST_PARAM CLOB
)
/

comment on table SAP_DELIVERY_NOTE_LOG is '单据日志信息表'
/

comment on column SAP_DELIVERY_NOTE_LOG.UUID is '主键UUID-java生成'
/

comment on column SAP_DELIVERY_NOTE_LOG.DOCUMENT_NUMBER is '单据号/交货单日志记录交货单号；上架下架任务日志记录上架下架任务uuid；出库任务记录出库任务uuid；'
/

comment on column SAP_DELIVERY_NOTE_LOG.USER_ID is '用户工号'
/

comment on column SAP_DELIVERY_NOTE_LOG.OPERATION_TYPE is '操作类型'
/

comment on column SAP_DELIVERY_NOTE_LOG.COMMENTS is '操作内容/评论'
/

comment on column SAP_DELIVERY_NOTE_LOG.RELATE_DOCUMENT_NUMBER is '关联单据号(记录上架任务关联的交货单号)'
/

comment on column SAP_DELIVERY_NOTE_LOG.CREATE_TIME is '创建时间'
/

comment on column SAP_DELIVERY_NOTE_LOG.LOG_AUTO_ID is '单据日志信息表自增ID'
/

comment on column SAP_DELIVERY_NOTE_LOG.REMARK is '备注信息/SAP返回数据'
/

comment on column SAP_DELIVERY_NOTE_LOG.REQUEST_PARAM is '请求参数'
/

create index INDEX_DN_LOG_AUTO_ID
	on SAP_DELIVERY_NOTE_LOG (LOG_AUTO_ID)
/

create trigger TR_SAP_DELIVERY_NOTE_LOG_BEF
	before insert
	on SAP_DELIVERY_NOTE_LOG
	for each row
DECLARE
    V_NUM1 NUMBER := 0;
BEGIN
    SELECT NVL(TO_NUMBER(MAX(DNL.LOG_AUTO_ID)), 0) INTO V_NUM1 FROM SAP_DELIVERY_NOTE_LOG DNL;
        :NEW.LOG_AUTO_ID := V_NUM1 + 1;
END;
/

create table WHS_OPERATE_LOG
(
	LOG_ID VARCHAR2(32) not null
		constraint WHS_OPERATE_LOG_PK
			primary key,
	DOCUMENT_TYPE VARCHAR2(2),
	UUID_STORAGE VARCHAR2(32),
	MATERIAL_NO VARCHAR2(40),
	BOX_NOTE VARCHAR2(50),
	QUANTITY NUMBER,
	OPERATION_TYPE VARCHAR2(2),
	DELIVERY_NOTE_SAP VARCHAR2(16),
	RELATE_DOCUMENT_NUMBER VARCHAR2(32),
	COMMENTS VARCHAR2(255),
	USER_ID VARCHAR2(60),
	CREATE_TIME DATE default TO_DATE(TO_CHAR(SYS_EXTRACT_UTC(SYSTIMESTAMP), 'yyyy-mm-dd hh24:mi:ss'), 'yyyy-mm-dd hh24:mi:ss'),
	REMARK VARCHAR2(255),
	LOG_AUTO_ID NUMBER,
	WERKS VARCHAR2(6),
	WHS_LOCATION_CODE VARCHAR2(6),
	MOVEMENT_TYPE VARCHAR2(32)
)
/

comment on table WHS_OPERATE_LOG is '库存操作日志表'
/

comment on column WHS_OPERATE_LOG.LOG_ID is '日志表主键UUID'
/

comment on column WHS_OPERATE_LOG.DOCUMENT_TYPE is '日志单据类型：1自管仓仓库库存日志；2自管仓储位库存日志；3海外仓仓库库存日志；4海外仓储位库存日志；'
/

comment on column WHS_OPERATE_LOG.UUID_STORAGE is '储位UUID-储位库存变化时使用'
/

comment on column WHS_OPERATE_LOG.MATERIAL_NO is '物料编码'
/

comment on column WHS_OPERATE_LOG.BOX_NOTE is '箱码，自管仓备件货出入库、上下架时使用'
/

comment on column WHS_OPERATE_LOG.QUANTITY is '数量'
/

comment on column WHS_OPERATE_LOG.OPERATION_TYPE is '操作类型:1入库(加仓库库存);2上架(加储位库存);3下架(减储位库存);4出库(减仓库库存);5移位(出储位);6移位(进储位);7盘点(盘盈入储位);8盘点(盘亏出储位);9盘点(盘盈入仓库);10盘点(盘亏出仓库);11移仓(出储位);12移仓(入储位);13移仓(出仓库);14移仓(入仓库);15转储(出仓库);16转储(出储位);17转储(入仓库);18转储(入储位);19撤销(入储位);20撤销(出储位);21海外箱码收货;22海外撤销箱码收货;'
/

comment on column WHS_OPERATE_LOG.DELIVERY_NOTE_SAP is '交货单号'
/

comment on column WHS_OPERATE_LOG.RELATE_DOCUMENT_NUMBER is '关联单据号(预留)/出库任务uuid'
/

comment on column WHS_OPERATE_LOG.COMMENTS is '操作内容/评论'
/

comment on column WHS_OPERATE_LOG.USER_ID is '用户工号'
/

comment on column WHS_OPERATE_LOG.CREATE_TIME is '创建时间'
/

comment on column WHS_OPERATE_LOG.REMARK is '备注说明'
/

comment on column WHS_OPERATE_LOG.LOG_AUTO_ID is '日志表自增ID'
/

comment on column WHS_OPERATE_LOG.WERKS is '工厂代码'
/

comment on column WHS_OPERATE_LOG.WHS_LOCATION_CODE is '仓库代码'
/

comment on column WHS_OPERATE_LOG.MOVEMENT_TYPE is '移动类型'
/

create index INDEX_WHS_OPERATE_LOG_AUTO_ID
	on WHS_OPERATE_LOG (LOG_AUTO_ID)
/

create trigger TR_WHS_OPERATE_LOG_BEF
	before insert
	on WHS_OPERATE_LOG
	for each row
DECLARE
    V_NUM1 NUMBER := 0;
BEGIN
    SELECT NVL(TO_NUMBER(MAX(LOG.LOG_AUTO_ID)), 0) INTO V_NUM1 FROM WHS_OPERATE_LOG LOG;
        :NEW.LOG_AUTO_ID := V_NUM1 + 1;
END;
/

create table ORDER_COST_DEPT
(
	UUID VARCHAR2(32) not null
		constraint ORDER_COST_DEPT_PK
			primary key,
	DEPT_ID NUMBER,
	DEPT_NAME VARCHAR2(255),
	SUPERIOR_DEPT NUMBER
)
/

comment on table ORDER_COST_DEPT is '订单费用承担部门'
/

comment on column ORDER_COST_DEPT.UUID is 'UUID主键'
/

comment on column ORDER_COST_DEPT.DEPT_ID is '部门ID'
/

comment on column ORDER_COST_DEPT.DEPT_NAME is '部门名称'
/

comment on column ORDER_COST_DEPT.SUPERIOR_DEPT is '上级部门ID(0为根节点，负ID不可用)'
/

create table SAP_PACKAGE_LIST
(
	PACKAGE_LIST VARCHAR2(10) not null
		constraint SAP_PACKAGE_LIST_PK
			primary key,
	CUSTOMER VARCHAR2(50),
	SPARE_PARTS VARCHAR2(10),
	PLANNED_CABINETS_QTY NUMBER,
	FROM_PORT VARCHAR2(50),
	FROM_PORT_NAME VARCHAR2(255),
	TO_PORT VARCHAR2(255),
	TO_PORT_NAME VARCHAR2(255),
	SHIP_COMPANY VARCHAR2(128),
	CABINET_MODEL VARCHAR2(4),
	PLANNER VARCHAR2(128),
	LOADER VARCHAR2(128),
	ACTUAL_LOADING_DATE VARCHAR2(10),
	SPMS_STATUS VARCHAR2(2) default 0,
	CREATE_TIME DATE default TO_DATE(TO_CHAR(SYS_EXTRACT_UTC(SYSTIMESTAMP), 'yyyy-mm-dd hh24:mi:ss'), 'yyyy-mm-dd hh24:mi:ss'),
	UPDATE_TIME DATE default TO_DATE(TO_CHAR(SYS_EXTRACT_UTC(SYSTIMESTAMP), 'yyyy-mm-dd hh24:mi:ss'), 'yyyy-mm-dd hh24:mi:ss')
)
/

comment on table SAP_PACKAGE_LIST is 'SAP装箱单表抬头'
/

comment on column SAP_PACKAGE_LIST.PACKAGE_LIST is '装箱单号'
/

comment on column SAP_PACKAGE_LIST.CUSTOMER is '客户'
/

comment on column SAP_PACKAGE_LIST.SPARE_PARTS is '是否杂货'
/

comment on column SAP_PACKAGE_LIST.PLANNED_CABINETS_QTY is '计划柜量'
/

comment on column SAP_PACKAGE_LIST.FROM_PORT is '起运港'
/

comment on column SAP_PACKAGE_LIST.FROM_PORT_NAME is '起运港名称'
/

comment on column SAP_PACKAGE_LIST.TO_PORT is '目的港'
/

comment on column SAP_PACKAGE_LIST.TO_PORT_NAME is '目的港名称'
/

comment on column SAP_PACKAGE_LIST.SHIP_COMPANY is '船公司'
/

comment on column SAP_PACKAGE_LIST.CABINET_MODEL is '柜型'
/

comment on column SAP_PACKAGE_LIST.PLANNER is '计划员'
/

comment on column SAP_PACKAGE_LIST.LOADER is '装柜员'
/

comment on column SAP_PACKAGE_LIST.ACTUAL_LOADING_DATE is '实际装柜日期'
/

comment on column SAP_PACKAGE_LIST.SPMS_STATUS is '任务在SPMS状态(0待处理；1处理中；2处理完成)'
/

comment on column SAP_PACKAGE_LIST.CREATE_TIME is 'SPMS创建时间'
/

comment on column SAP_PACKAGE_LIST.UPDATE_TIME is 'SPMS更新时间'
/

create trigger TR_SAP_PACKAGE_LIST_BEF
	before update
	on SAP_PACKAGE_LIST
	for each row
BEGIN
    :new.UPDATE_TIME := TO_DATE(TO_CHAR(SYS_EXTRACT_UTC(SYSTIMESTAMP), 'yyyy-mm-dd hh24:mi:ss'), 'yyyy-mm-dd hh24:mi:ss');
END;
/

create table SAP_PACKAGE_LIST_DTL
(
	PACKAGE_LIST VARCHAR2(10),
	ITEM VARCHAR2(6),
	DATE_OF_LOADING VARCHAR2(10),
	LOADING_WERKS VARCHAR2(6),
	LOADING_WHS_LOCATION_CODE VARCHAR2(4),
	CABINET_SERIAL_NUMBER VARCHAR2(2),
	GOODS_TYPE VARCHAR2(30),
	RECEIVING_WERKS VARCHAR2(6),
	ACTUAL_RECEIVING_WERKS VARCHAR2(6),
	CABINET_NUMBER VARCHAR2(16),
	LEAD_SEAL VARCHAR2(16),
	MATERIAL_NO VARCHAR2(18),
	SHIPMENT_CATEGORY VARCHAR2(128),
	PLANNED_PACKING_QTY NUMBER(17,3),
	BASIC_UNIT VARCHAR2(3),
	PLANNED_PACKING_NUMBER NUMBER(17,3),
	PACKING_UNIT VARCHAR2(3),
	CABINET_WEIGHT NUMBER(17,6),
	WEIGHT_UNIT VARCHAR2(6),
	CABINET_VOLUME NUMBER(17,6),
	VOLUME_UNIT VARCHAR2(6),
	TRAILER_LINE VARCHAR2(64),
	TRAILER_LINE_DESC VARCHAR2(128),
	SPMS_STATUS VARCHAR2(2) default 0,
	WHS_OUT_UUID VARCHAR2(32),
	TEMP_STORAGE VARCHAR2(4000),
	UNPLANNED VARCHAR2(2),
	ACTUAL_OPERATE_QTY NUMBER(17,3),
	CONTAINER_BRAND VARCHAR2(64),
	COLOR_NUMBER VARCHAR2(32),
	THICKNESS NUMBER(7,3),
	ACTUAL_AVG_GROSS_WEIGHT NUMBER(7,3),
	ACTUAL_PACKING_NUMBER NUMBER(17,3),
	CUSTOMS_DECLARATION_NUMBER NUMBER(17,3),
	PACKING_SPEC VARCHAR2(128),
	LOADING_TYPE VARCHAR2(128),
	CREATE_TIME DATE default TO_DATE(TO_CHAR(SYS_EXTRACT_UTC(SYSTIMESTAMP), 'yyyy-mm-dd hh24:mi:ss'), 'yyyy-mm-dd hh24:mi:ss'),
	UPDATE_TIME DATE default TO_DATE(TO_CHAR(SYS_EXTRACT_UTC(SYSTIMESTAMP), 'yyyy-mm-dd hh24:mi:ss'), 'yyyy-mm-dd hh24:mi:ss')
)
/

comment on table SAP_PACKAGE_LIST_DTL is 'SAP装箱单明细表'
/

comment on column SAP_PACKAGE_LIST_DTL.PACKAGE_LIST is '装箱单号'
/

comment on column SAP_PACKAGE_LIST_DTL.ITEM is '装箱单行号'
/

comment on column SAP_PACKAGE_LIST_DTL.DATE_OF_LOADING is '可装柜日期'
/

comment on column SAP_PACKAGE_LIST_DTL.LOADING_WERKS is '装柜工厂代码'
/

comment on column SAP_PACKAGE_LIST_DTL.LOADING_WHS_LOCATION_CODE is '装柜仓库代码'
/

comment on column SAP_PACKAGE_LIST_DTL.CABINET_SERIAL_NUMBER is '柜序号'
/

comment on column SAP_PACKAGE_LIST_DTL.GOODS_TYPE is '货物类型/产品层次'
/

comment on column SAP_PACKAGE_LIST_DTL.RECEIVING_WERKS is '收货工厂'
/

comment on column SAP_PACKAGE_LIST_DTL.ACTUAL_RECEIVING_WERKS is '实际收货工厂'
/

comment on column SAP_PACKAGE_LIST_DTL.CABINET_NUMBER is '柜号'
/

comment on column SAP_PACKAGE_LIST_DTL.LEAD_SEAL is '铅封号'
/

comment on column SAP_PACKAGE_LIST_DTL.MATERIAL_NO is '物料编号'
/

comment on column SAP_PACKAGE_LIST_DTL.SHIPMENT_CATEGORY is '出货品类'
/

comment on column SAP_PACKAGE_LIST_DTL.PLANNED_PACKING_QTY is '计划装箱数量(基本单位)'
/

comment on column SAP_PACKAGE_LIST_DTL.BASIC_UNIT is '基本单位'
/

comment on column SAP_PACKAGE_LIST_DTL.PLANNED_PACKING_NUMBER is '计划装箱件数'
/

comment on column SAP_PACKAGE_LIST_DTL.PACKING_UNIT is '包装单位'
/

comment on column SAP_PACKAGE_LIST_DTL.CABINET_WEIGHT is '柜重量'
/

comment on column SAP_PACKAGE_LIST_DTL.WEIGHT_UNIT is '重量单位'
/

comment on column SAP_PACKAGE_LIST_DTL.CABINET_VOLUME is '柜体积'
/

comment on column SAP_PACKAGE_LIST_DTL.VOLUME_UNIT is '体积单位'
/

comment on column SAP_PACKAGE_LIST_DTL.TRAILER_LINE is '出发国拖车线路'
/

comment on column SAP_PACKAGE_LIST_DTL.TRAILER_LINE_DESC is '出发国拖车线路名称'
/

comment on column SAP_PACKAGE_LIST_DTL.SPMS_STATUS is '装箱单行项目在SPMS状态，默认0(0待执行，1执行中，2出库完成，3任务关闭，4提交SAP成功)'
/

comment on column SAP_PACKAGE_LIST_DTL.WHS_OUT_UUID is '出库任务UUID-贸易货'
/

comment on column SAP_PACKAGE_LIST_DTL.TEMP_STORAGE is '储位信息。用于存储贸易货储位uuid 数量等信息'
/

comment on column SAP_PACKAGE_LIST_DTL.UNPLANNED is '是否为计划外的明细。默认空，额外出库的贸易货需传值1'
/

comment on column SAP_PACKAGE_LIST_DTL.ACTUAL_OPERATE_QTY is '贸易货实际装箱数量(基本单位)'
/

comment on column SAP_PACKAGE_LIST_DTL.CONTAINER_BRAND is '外箱品牌'
/

comment on column SAP_PACKAGE_LIST_DTL.COLOR_NUMBER is '色号（砖类）'
/

comment on column SAP_PACKAGE_LIST_DTL.THICKNESS is '砖厚'
/

comment on column SAP_PACKAGE_LIST_DTL.ACTUAL_AVG_GROSS_WEIGHT is '产品实际平均毛重'
/

comment on column SAP_PACKAGE_LIST_DTL.ACTUAL_PACKING_NUMBER is '实际装箱件数（销售单位）'
/

comment on column SAP_PACKAGE_LIST_DTL.CUSTOMS_DECLARATION_NUMBER is '报关件数'
/

comment on column SAP_PACKAGE_LIST_DTL.PACKING_SPEC is '包装规格'
/

comment on column SAP_PACKAGE_LIST_DTL.LOADING_TYPE is '装柜方式'
/

comment on column SAP_PACKAGE_LIST_DTL.CREATE_TIME is 'SPMS创建时间'
/

comment on column SAP_PACKAGE_LIST_DTL.UPDATE_TIME is 'SPMS更新时间'
/

create trigger TR_SAP_PACKAGE_LIST_DTL_BEF
	before insert
	on SAP_PACKAGE_LIST_DTL
	for each row
DECLARE
    V_NUM1 NUMBER := 0;
BEGIN
    IF (NVL(:NEW.ITEM, 'IS NULL') = 'IS NULL')
        THEN
            SELECT NVL(TO_NUMBER(MAX(TO_NUMBER(PLD.ITEM))), 0) INTO V_NUM1 FROM SAP_PACKAGE_LIST_DTL PLD
            WHERE PLD.PACKAGE_LIST = :NEW.PACKAGE_LIST;
            :NEW.ITEM := TO_CHAR(V_NUM1 + 10);
    END IF;
END;
/

create trigger TR_SAP_PACKAGE_LIST_DTL_BEF_UPDATE
	before update
	on SAP_PACKAGE_LIST_DTL
	for each row
BEGIN
    :new.UPDATE_TIME := TO_DATE(TO_CHAR(SYS_EXTRACT_UTC(SYSTIMESTAMP), 'yyyy-mm-dd hh24:mi:ss'), 'yyyy-mm-dd hh24:mi:ss');
END;
/

create table SAP_PACKAGE_BOX
(
	PACKAGE_LIST VARCHAR2(10),
	ITEM VARCHAR2(6),
	BOX_NOTE VARCHAR2(50),
	ACTUAL_OPERATE_QTY NUMBER,
	UNPLANNED VARCHAR2(2),
	WHS_OUT_UUID VARCHAR2(32),
	CONTAINER_BRAND VARCHAR2(64),
	PACKING_SPEC VARCHAR2(128),
	LOADING_TYPE VARCHAR2(128),
	CREATE_TIME DATE default TO_DATE(TO_CHAR(SYS_EXTRACT_UTC(SYSTIMESTAMP), 'yyyy-mm-dd hh24:mi:ss'), 'yyyy-mm-dd hh24:mi:ss')
)
/

comment on table SAP_PACKAGE_BOX is '装箱单箱码关系表'
/

comment on column SAP_PACKAGE_BOX.PACKAGE_LIST is '装箱单号'
/

comment on column SAP_PACKAGE_BOX.ITEM is '装箱单行号'
/

comment on column SAP_PACKAGE_BOX.BOX_NOTE is '箱码'
/

comment on column SAP_PACKAGE_BOX.ACTUAL_OPERATE_QTY is '实际操作出库数量'
/

comment on column SAP_PACKAGE_BOX.UNPLANNED is '是否计划外的箱码，默认空，1表示计划外的箱码'
/

comment on column SAP_PACKAGE_BOX.WHS_OUT_UUID is '新增计划外的箱码时，存入出库任务uuid关联'
/

comment on column SAP_PACKAGE_BOX.CONTAINER_BRAND is '外箱品牌'
/

comment on column SAP_PACKAGE_BOX.PACKING_SPEC is '包装规格'
/

comment on column SAP_PACKAGE_BOX.LOADING_TYPE is '装柜方式'
/

comment on column SAP_PACKAGE_BOX.CREATE_TIME is 'SPMS创建时间'
/

create table WHS_OUT
(
	UUID VARCHAR2(32) not null
		constraint WHS_OUT_PK
			primary key,
	WHS_OUT_NO VARCHAR2(32),
	WERKS VARCHAR2(6),
	WHS_LOCATION_CODE VARCHAR2(6),
	PACKAGE_LIST VARCHAR2(10),
	GOODS_TYPE VARCHAR2(30),
	DATE_OF_LOADING VARCHAR2(10),
	CABINET_NUMBER VARCHAR2(16),
	CABINET_SERIAL_NUMBER VARCHAR2(2),
	TASK_STATUS VARCHAR2(2),
	CREATE_USERID VARCHAR2(20),
	CREATE_DATE VARCHAR2(10),
	CREATE_TIME DATE default TO_DATE(TO_CHAR(SYS_EXTRACT_UTC(SYSTIMESTAMP), 'yyyy-mm-dd hh24:mi:ss'), 'yyyy-mm-dd hh24:mi:ss'),
	SAP_VOUCHER_NUMBER VARCHAR2(16),
	SAP_VOUCHER_YEAR VARCHAR2(4),
	ACTUAL_LOADING_DATE VARCHAR2(16),
	MONITOR_LOADING VARCHAR2(60),
	LOADING_TYPE VARCHAR2(60),
	REMARKS VARCHAR2(255),
	CABINET_CHECK_RESULT VARCHAR2(128),
	LEAD_SEAL VARCHAR2(32),
	EMPTY_CABINET_WEIGHT NUMBER(13,3) default 0.000,
	OPERATE_USER VARCHAR2(255)
)
/

comment on table WHS_OUT is '出库任务表'
/

comment on column WHS_OUT.UUID is '出库任务表主键UUID'
/

comment on column WHS_OUT.WHS_OUT_NO is '出库任务单号-工厂+仓库+日期+三位流水'
/

comment on column WHS_OUT.WERKS is '工厂代码'
/

comment on column WHS_OUT.WHS_LOCATION_CODE is '仓库地点代码/可装柜地点代码'
/

comment on column WHS_OUT.PACKAGE_LIST is '装箱单号'
/

comment on column WHS_OUT.GOODS_TYPE is '货物类型'
/

comment on column WHS_OUT.DATE_OF_LOADING is '可装柜日期'
/

comment on column WHS_OUT.CABINET_NUMBER is '柜号'
/

comment on column WHS_OUT.CABINET_SERIAL_NUMBER is '柜序号'
/

comment on column WHS_OUT.TASK_STATUS is '出库任务状态:0待处理，1处理中，2待提交SAP，3已关闭，4提交SAP成功，5提交SAP失败'
/

comment on column WHS_OUT.CREATE_USERID is '出库任务创建人工号'
/

comment on column WHS_OUT.CREATE_DATE is '创建日期'
/

comment on column WHS_OUT.CREATE_TIME is '创建时间'
/

comment on column WHS_OUT.SAP_VOUCHER_NUMBER is 'SAP物料凭证号'
/

comment on column WHS_OUT.SAP_VOUCHER_YEAR is 'SAP凭证年份'
/

comment on column WHS_OUT.ACTUAL_LOADING_DATE is '实际装柜日期'
/

comment on column WHS_OUT.MONITOR_LOADING is '监装人员'
/

comment on column WHS_OUT.LOADING_TYPE is '装柜方式（仅参考）'
/

comment on column WHS_OUT.REMARKS is '备注信息'
/

comment on column WHS_OUT.CABINET_CHECK_RESULT is '货柜检验结果'
/

comment on column WHS_OUT.LEAD_SEAL is '铅封号'
/

comment on column WHS_OUT.EMPTY_CABINET_WEIGHT is '空柜重量（KG）'
/

comment on column WHS_OUT.OPERATE_USER is '操作员/现场搬运工计费'
/

create trigger TR_WHS_OUT_BEF
	before insert
	on WHS_OUT
	for each row
DECLARE
    V_NUM1 NUMBER := 0;
BEGIN
    SELECT NVL(TO_NUMBER(SUBSTR(MAX(WO.WHS_OUT_NO), -3, 3)), 0) INTO V_NUM1 FROM WHS_OUT WO
    WHERE WO.CREATE_DATE = :NEW.CREATE_DATE
      AND WO.WERKS = :NEW.WERKS
      AND WO.WHS_LOCATION_CODE = :NEW.WHS_LOCATION_CODE;
    :NEW.WHS_OUT_NO := :NEW.WERKS||'-'||:NEW.WHS_LOCATION_CODE||'-'||REPLACE(:NEW.CREATE_DATE, '-')||'-'|| SUBSTR(1001 + V_NUM1, 2, 4);
END;
/

create table WITHDRAW_TASK
(
	UUID VARCHAR2(32) not null
		constraint WITHDRAW_TASK_PK
			primary key,
	TASK_ID VARCHAR2(32),
	WERKS VARCHAR2(6),
	WHS_LOCATION_CODE VARCHAR2(6),
	CREATE_USERID VARCHAR2(64),
	CREATE_DATE VARCHAR2(16),
	TASK_STATUS VARCHAR2(2),
	CREATE_TIME DATE default TO_DATE(TO_CHAR(SYS_EXTRACT_UTC(SYSTIMESTAMP), 'yyyy-mm-dd hh24:mi:ss'), 'yyyy-mm-dd hh24:mi:ss'),
	DELIVERY_NOTE_SAP VARCHAR2(10),
	PDA_SN VARCHAR2(32),
	MOBILE_TYPE VARCHAR2(8),
	TASK_CATEGORY VARCHAR2(2),
	RELATED_TASK_ID VARCHAR2(32),
	SAP_VOUCHER_NUMBER VARCHAR2(32),
	SAP_VOUCHER_YEAR VARCHAR2(4)
)
/

comment on table WITHDRAW_TASK is '移动冲销表'
/

comment on column WITHDRAW_TASK.UUID is '冲销任务UUID'
/

comment on column WITHDRAW_TASK.TASK_ID is '冲销任务单号'
/

comment on column WITHDRAW_TASK.WERKS is '工厂编号'
/

comment on column WITHDRAW_TASK.WHS_LOCATION_CODE is '仓库地点代码'
/

comment on column WITHDRAW_TASK.CREATE_USERID is '创建人工号'
/

comment on column WITHDRAW_TASK.CREATE_DATE is '创建日期'
/

comment on column WITHDRAW_TASK.TASK_STATUS is '任务状态：1 已完成'
/

comment on column WITHDRAW_TASK.CREATE_TIME is '创建时间'
/

comment on column WITHDRAW_TASK.DELIVERY_NOTE_SAP is '交货单号'
/

comment on column WITHDRAW_TASK.PDA_SN is 'PDA设备编号'
/

comment on column WITHDRAW_TASK.MOBILE_TYPE is '101采购入库；102出库冲销；'
/

comment on column WITHDRAW_TASK.TASK_CATEGORY is '任备类别 X 备件货，M 贸易货'
/

comment on column WITHDRAW_TASK.RELATED_TASK_ID is '关联单号'
/

comment on column WITHDRAW_TASK.SAP_VOUCHER_NUMBER is 'SAP 凭证号'
/

comment on column WITHDRAW_TASK.SAP_VOUCHER_YEAR is 'SAP 凭证年度'
/

create trigger TR_WITHDRAW_TASK_BEF1
	before insert
	on WITHDRAW_TASK
	for each row
DECLARE
    V_NUM1 NUMBER := 0;
BEGIN
    SELECT NVL(TO_NUMBER(SUBSTR(MAX(SOT.TASK_ID), -3, 3)), 0) INTO V_NUM1 FROM WITHDRAW_TASK SOT
    WHERE SOT.WERKS = :NEW.WERKS
      AND SOT.WHS_LOCATION_CODE = :NEW.WHS_LOCATION_CODE;
    :NEW.TASK_ID := 'CX-' || :NEW.WERKS||'-'||:NEW.WHS_LOCATION_CODE||'-'||SUBSTR(1001 + V_NUM1, 2, 4);
END;
/

create table WITHDRAW_TASK_DTL
(
	UUID VARCHAR2(32),
	TASK_ROW_ID VARCHAR2(6),
	MATERIAL_NO VARCHAR2(32),
	BOX_NOTE VARCHAR2(50),
	QUANTITY NUMBER,
	STORAGE VARCHAR2(32),
	DELIVERY_NOTE_SAP VARCHAR2(10)
)
/

comment on table WITHDRAW_TASK_DTL is '冲销任务表明细'
/

comment on column WITHDRAW_TASK_DTL.UUID is '冲销任务UUID'
/

comment on column WITHDRAW_TASK_DTL.TASK_ROW_ID is '冲销任务行号'
/

comment on column WITHDRAW_TASK_DTL.MATERIAL_NO is '物料编号'
/

comment on column WITHDRAW_TASK_DTL.BOX_NOTE is '箱码-仅用于自管仓备件管理'
/

comment on column WITHDRAW_TASK_DTL.QUANTITY is '数量'
/

comment on column WITHDRAW_TASK_DTL.STORAGE is '储位UUID'
/

comment on column WITHDRAW_TASK_DTL.DELIVERY_NOTE_SAP is 'SAP交货单号'
/

create table MATERIAL_CATEGORY
(
	CATEGORY_ID NUMBER not null
		primary key,
	CATEGORY_LEVEL NUMBER not null,
	PARENT_ID NUMBER default 0 not null,
	CATEGORY_NAME VARCHAR2(128) not null,
	CATEGORY_RANK NUMBER default 0 not null,
	DESCRIPTION VARCHAR2(128),
	CREATE_USERID VARCHAR2(32),
	CREATE_TIME DATE default TO_DATE(TO_CHAR(SYS_EXTRACT_UTC(SYSTIMESTAMP), 'yyyy-mm-dd hh24:mi:ss'),
                                     'yyyy-mm-dd hh24:mi:ss'),
	UPDATE_USERID VARCHAR2(32),
	UPDATE_TIME DATE,
	IS_DELETED VARCHAR2(8) default '0' not null,
	CATEGORY_EN_NAME VARCHAR2(50)
)
/

comment on table MATERIAL_CATEGORY is '物料目录表'
/

comment on column MATERIAL_CATEGORY.CATEGORY_ID is '物料分类编码'
/

comment on column MATERIAL_CATEGORY.CATEGORY_LEVEL is '物料分类级别'
/

comment on column MATERIAL_CATEGORY.PARENT_ID is '父分类ID'
/

comment on column MATERIAL_CATEGORY.CATEGORY_NAME is '物料分类名称'
/

comment on column MATERIAL_CATEGORY.CATEGORY_RANK is '排序值（字段越大越靠前）'
/

comment on column MATERIAL_CATEGORY.DESCRIPTION is '目录描述'
/

comment on column MATERIAL_CATEGORY.CREATE_USERID is '创建者ID'
/

comment on column MATERIAL_CATEGORY.CREATE_TIME is '创建时间'
/

comment on column MATERIAL_CATEGORY.UPDATE_USERID is '更新者ID'
/

comment on column MATERIAL_CATEGORY.UPDATE_TIME is '更新时间'
/

comment on column MATERIAL_CATEGORY.IS_DELETED is '是否删除-1是；默认0否'
/

comment on column MATERIAL_CATEGORY.CATEGORY_EN_NAME is '物料分类英文名称'
/

create trigger TR_MATERIAL_CATEGORY_BEF
	before update
	on MATERIAL_CATEGORY
	for each row
BEGIN
    :new.UPDATE_TIME :=
            TO_DATE(TO_CHAR(SYS_EXTRACT_UTC(SYSTIMESTAMP), 'yyyy-mm-dd hh24:mi:ss'), 'yyyy-mm-dd hh24:mi:ss');
END;
/

create table SYS_LOG_MQ
(
	LOG_AUTO_ID NUMBER,
	UUID VARCHAR2(32) not null
		constraint SYS_LOG_MQ_PK
			primary key,
	LOG_TYPE VARCHAR2(2),
	REQUEST_INTERFACE VARCHAR2(255),
	REQUEST_DESC VARCHAR2(255),
	FUNCTION_ID VARCHAR2(128),
	USER_ID VARCHAR2(60),
	CREATE_TIME DATE default TO_DATE(TO_CHAR(SYS_EXTRACT_UTC(SYSTIMESTAMP), 'yyyy-mm-dd hh24:mi:ss'), 'yyyy-mm-dd hh24:mi:ss'),
	REQUEST_PARAM CLOB,
	REMARKS CLOB,
	RECEIVED_MESSAGE CLOB
)
/

comment on table SYS_LOG_MQ is 'MQ系统日志表'
/

comment on column SYS_LOG_MQ.LOG_AUTO_ID is 'MQ日志表自增ID'
/

comment on column SYS_LOG_MQ.UUID is '唯一UUID'
/

comment on column SYS_LOG_MQ.LOG_TYPE is '日志类型（1发送MQ日志，2接收MQ日志，3发送rest请求，4接收rest请求）'
/

comment on column SYS_LOG_MQ.REQUEST_INTERFACE is '接口名称'
/

comment on column SYS_LOG_MQ.REQUEST_DESC is '功能描述'
/

comment on column SYS_LOG_MQ.FUNCTION_ID is 'FunctionId'
/

comment on column SYS_LOG_MQ.USER_ID is '创建人工号'
/

comment on column SYS_LOG_MQ.CREATE_TIME is '创建时间'
/

create index INDEX_SYS_LOG_MQ_AUTO_ID
	on SYS_LOG_MQ (LOG_AUTO_ID)
/

create trigger TR_SYS_LOG_MQ_BEF
	before insert
	on SYS_LOG_MQ
	for each row
DECLARE
    V_NUM1 NUMBER := 0;
BEGIN
    SELECT NVL(TO_NUMBER(MAX(LOG.LOG_AUTO_ID)), 0) INTO V_NUM1 FROM SYS_LOG_MQ LOG;
    :NEW.LOG_AUTO_ID := V_NUM1 + 1;
END;
/

create table SHELF_ON_TASK
(
	UUID VARCHAR2(32) not null
		constraint SHELF_ON_TASK_PK
			primary key,
	TASK_ID VARCHAR2(32),
	WERKS VARCHAR2(6),
	WHS_LOCATION_CODE VARCHAR2(6),
	CREATE_USERID VARCHAR2(64),
	CREATE_DATE VARCHAR2(16),
	TASK_STATUS VARCHAR2(2),
	CREATE_TIME DATE default TO_DATE(TO_CHAR(SYS_EXTRACT_UTC(SYSTIMESTAMP), 'yyyy-mm-dd hh24:mi:ss'), 'yyyy-mm-dd hh24:mi:ss'),
	UPDATE_USERNAME VARCHAR2(64),
	UPDATE_TIME DATE,
	DELIVERY_NOTE_SAP VARCHAR2(10),
	PDA_SN VARCHAR2(32),
	TASK_TYPE VARCHAR2(2),
	RELATE_DOC_NUMBER VARCHAR2(32)
)
/

comment on table SHELF_ON_TASK is '上架下架任务表'
/

comment on column SHELF_ON_TASK.UUID is '上架下架任务UUID'
/

comment on column SHELF_ON_TASK.TASK_ID is '上架下架任务单号'
/

comment on column SHELF_ON_TASK.WERKS is '工厂编号'
/

comment on column SHELF_ON_TASK.WHS_LOCATION_CODE is '仓库地点代码'
/

comment on column SHELF_ON_TASK.CREATE_USERID is '创建人工号'
/

comment on column SHELF_ON_TASK.CREATE_DATE is '创建日期'
/

comment on column SHELF_ON_TASK.TASK_STATUS is '任务状态：1待上架；2上架中；3已完成；4待下架；5下架中；6出库待下架；7出库下架中；只有完成状态才更新储位库存。'
/

comment on column SHELF_ON_TASK.CREATE_TIME is '创建时间'
/

comment on column SHELF_ON_TASK.UPDATE_USERNAME is '更新人姓名'
/

comment on column SHELF_ON_TASK.UPDATE_TIME is '更新时间'
/

comment on column SHELF_ON_TASK.DELIVERY_NOTE_SAP is '交货单号'
/

comment on column SHELF_ON_TASK.PDA_SN is 'PDA设备编号'
/

comment on column SHELF_ON_TASK.TASK_TYPE is '1上架任务；2下架任务；'
/

comment on column SHELF_ON_TASK.RELATE_DOC_NUMBER is '关联单据号/出库任务uuid'
/

create trigger TR_SHELF_ON_TASK_BEF
	before update
	on SHELF_ON_TASK
	for each row
BEGIN
    :new.UPDATE_TIME := TO_DATE(TO_CHAR(SYS_EXTRACT_UTC(SYSTIMESTAMP), 'yyyy-mm-dd hh24:mi:ss'), 'yyyy-mm-dd hh24:mi:ss');
END;
/

create trigger TR_SHELF_ON_TASK_BEF1
	before insert
	on SHELF_ON_TASK
	for each row
DECLARE
    V_NUM1 NUMBER := 0;
BEGIN
    SELECT NVL(TO_NUMBER(SUBSTR(MAX(SOT.TASK_ID), -3, 3)), 0) INTO V_NUM1 FROM SHELF_ON_TASK SOT
    WHERE SOT.CREATE_DATE = :NEW.CREATE_DATE
      AND SOT.WERKS = :NEW.WERKS
      AND SOT.WHS_LOCATION_CODE = :NEW.WHS_LOCATION_CODE;
    :NEW.TASK_ID := :NEW.WERKS||'-'||:NEW.WHS_LOCATION_CODE||'-'||REPLACE(:NEW.CREATE_DATE, '-')||'-'||SUBSTR(1001 + V_NUM1, 2, 4);
END;
/

create table SHELF_ON_TASK_DTL
(
	UUID VARCHAR2(32),
	TASK_ROW_ID VARCHAR2(6),
	MATERIAL_NO VARCHAR2(32),
	BOX_NOTE VARCHAR2(50),
	QUANTITY NUMBER,
	CURRENT_STORAGE VARCHAR2(32),
	TARGET_STORAGE VARCHAR2(32),
	DELIVERY_NOTE_SAP VARCHAR2(10),
	ACTUAL_OPERATE_QTY NUMBER,
	UNPLANNED VARCHAR2(2)
)
/

comment on table SHELF_ON_TASK_DTL is '上架下架任务表明细'
/

comment on column SHELF_ON_TASK_DTL.UUID is '上架下架任务UUID'
/

comment on column SHELF_ON_TASK_DTL.TASK_ROW_ID is '上架下架任务行号'
/

comment on column SHELF_ON_TASK_DTL.MATERIAL_NO is '物料编号'
/

comment on column SHELF_ON_TASK_DTL.BOX_NOTE is '箱码-仅用于自管仓备件管理'
/

comment on column SHELF_ON_TASK_DTL.QUANTITY is '数量'
/

comment on column SHELF_ON_TASK_DTL.CURRENT_STORAGE is '当前储位UUID'
/

comment on column SHELF_ON_TASK_DTL.TARGET_STORAGE is '目标储位UUID'
/

comment on column SHELF_ON_TASK_DTL.DELIVERY_NOTE_SAP is 'SAP交货单号'
/

comment on column SHELF_ON_TASK_DTL.ACTUAL_OPERATE_QTY is '实际操作数量/实际下架数量'
/

comment on column SHELF_ON_TASK_DTL.UNPLANNED is '是否原单据行项目，默认空。出库任务用户可新增行，新增行赋值1.'
/

create table INVENTORY_CHECK
(
	UUID VARCHAR2(32) not null
		constraint INVENTORY_CHECK_PK
			primary key,
	INVENTORY_CHECK_ID VARCHAR2(32),
	WERKS VARCHAR2(6),
	WHS_LOCATION_CODE VARCHAR2(6),
	INVENTORY_CHECK_TYPE VARCHAR2(2),
	INVENTORY_CHECK_CATEGORY VARCHAR2(2),
	INVENTORY_CHECK_MOLD VARCHAR2(2),
	INVENTORY_CHECK_DATE VARCHAR2(16),
	INVENTORY_CHECK_RESULT VARCHAR2(2),
	IS_REVIEW VARCHAR2(2),
	INVENTORY_REVIEW_ID VARCHAR2(32),
	INVENTORY_CHECK_SURPLUS VARCHAR2(32),
	INVENTORY_CHECK_LOSS VARCHAR2(32),
	CREATE_DATE VARCHAR2(16),
	SPMS_STATUS VARCHAR2(2),
	REQUESTER VARCHAR2(32),
	OPERATOR VARCHAR2(32),
	REVIEWER VARCHAR2(32),
	APPROVER VARCHAR2(32),
	CREATE_TIME DATE default TO_DATE(TO_CHAR(SYS_EXTRACT_UTC(SYSTIMESTAMP), 'yyyy-mm-dd hh24:mi:ss'), 'yyyy-mm-dd hh24:mi:ss'),
	UPDATE_USERNAME VARCHAR2(64),
	UPDATE_TIME DATE
)
/

comment on table INVENTORY_CHECK is '仓库盘点任务表'
/

comment on column INVENTORY_CHECK.UUID is '仓库盘点任务UUID'
/

comment on column INVENTORY_CHECK.INVENTORY_CHECK_ID is '仓库盘点任务单号'
/

comment on column INVENTORY_CHECK.WERKS is '工厂编号'
/

comment on column INVENTORY_CHECK.WHS_LOCATION_CODE is '仓库地点代码'
/

comment on column INVENTORY_CHECK.INVENTORY_CHECK_TYPE is '是否杂货 X-备件货 M-贸易货'
/

comment on column INVENTORY_CHECK.INVENTORY_CHECK_CATEGORY is '盘点类别 1-日常盘点 2-月度盘点 3-动态盘点 4-其它盘点'
/

comment on column INVENTORY_CHECK.INVENTORY_CHECK_MOLD is '盘点类型 1-明盘 2-暗盘'
/

comment on column INVENTORY_CHECK.INVENTORY_CHECK_DATE is '盘点日期'
/

comment on column INVENTORY_CHECK.INVENTORY_CHECK_RESULT is '盘点结果 1-账实相符 2-盘盈 3-盘亏 4-盘盈盘亏'
/

comment on column INVENTORY_CHECK.IS_REVIEW is '是否复盘 1-初盘 2-复盘'
/

comment on column INVENTORY_CHECK.INVENTORY_REVIEW_ID is '复盘单据UUID'
/

comment on column INVENTORY_CHECK.INVENTORY_CHECK_SURPLUS is '盘盈单据号'
/

comment on column INVENTORY_CHECK.INVENTORY_CHECK_LOSS is '盘亏单据号'
/

comment on column INVENTORY_CHECK.CREATE_DATE is '创建日期'
/

comment on column INVENTORY_CHECK.SPMS_STATUS is '任务状态：1-待盘点；2-盘点中；3-复盘中；4-处理中；5-关闭(完成)'
/

comment on column INVENTORY_CHECK.REQUESTER is '创建人工号'
/

comment on column INVENTORY_CHECK.OPERATOR is '申请人'
/

comment on column INVENTORY_CHECK.REVIEWER is '复核人'
/

comment on column INVENTORY_CHECK.APPROVER is '审批人'
/

comment on column INVENTORY_CHECK.CREATE_TIME is '创建时间'
/

comment on column INVENTORY_CHECK.UPDATE_USERNAME is '更新人姓名'
/

comment on column INVENTORY_CHECK.UPDATE_TIME is '更新时间'
/

create trigger TR_INVENTORY_CHECK_BEF
	before update
	on INVENTORY_CHECK
	for each row
BEGIN
:new.UPDATE_TIME := TO_DATE(TO_CHAR(SYS_EXTRACT_UTC(SYSTIMESTAMP), 'yyyy-mm-dd hh24:mi:ss'), 'yyyy-mm-dd hh24:mi:ss');
END;
/

create trigger TR_INVENTORY_CHECK_BEF1
	before insert
	on INVENTORY_CHECK
	for each row
DECLARE
    V_NUM1 NUMBER := 0;
BEGIN
    SELECT NVL(TO_NUMBER(SUBSTR(MAX(SOT.INVENTORY_CHECK_ID), -3, 3)), 0) INTO V_NUM1 FROM INVENTORY_CHECK SOT
    WHERE SOT.CREATE_DATE = :NEW.CREATE_DATE
      AND SOT.WERKS = :NEW.WERKS
      AND SOT.WHS_LOCATION_CODE = :NEW.WHS_LOCATION_CODE;
    :NEW.INVENTORY_CHECK_ID := :NEW.WERKS||'-'||:NEW.WHS_LOCATION_CODE||'-'||REPLACE(:NEW.CREATE_DATE, '-')||'-'||SUBSTR(1001 + V_NUM1, 2, 4);
END;
/

create table INVENTORY_CHECK_DTL
(
	UUID VARCHAR2(32),
	TASK_ROW_ID VARCHAR2(6),
	MATERIAL_NO VARCHAR2(32),
	BOX_NOTE VARCHAR2(50),
	INVENTORY_QUANTITY NUMBER,
	CHECK_QUANTITY NUMBER,
	DISCREPANCY NUMBER,
	BASIC_UNIT VARCHAR2(32),
	STORAGE_UUID VARCHAR2(32),
	REMARKS VARCHAR2(256)
)
/

comment on table INVENTORY_CHECK_DTL is '盘点任务表明细表'
/

comment on column INVENTORY_CHECK_DTL.UUID is '盘点任务UUID'
/

comment on column INVENTORY_CHECK_DTL.TASK_ROW_ID is '盘点任务行号'
/

comment on column INVENTORY_CHECK_DTL.MATERIAL_NO is '物料编号'
/

comment on column INVENTORY_CHECK_DTL.BOX_NOTE is '箱码-仅用于自管仓备件管理'
/

comment on column INVENTORY_CHECK_DTL.INVENTORY_QUANTITY is '库存数量'
/

comment on column INVENTORY_CHECK_DTL.CHECK_QUANTITY is '实盘数量'
/

comment on column INVENTORY_CHECK_DTL.DISCREPANCY is '差异数量'
/

comment on column INVENTORY_CHECK_DTL.BASIC_UNIT is '基本单位'
/

comment on column INVENTORY_CHECK_DTL.STORAGE_UUID is '储位UUID'
/

comment on column INVENTORY_CHECK_DTL.REMARKS is '备注'
/

create table INVENTORY_CHECK_ACTION
(
	UUID VARCHAR2(32) not null
		constraint INVENTORY_CHECK_ACTION_PK
			primary key,
	SPMS_ID VARCHAR2(32),
	INVENTORY_ACTION_TYPE VARCHAR2(6),
	WERKS VARCHAR2(6),
	WHS_LOCATION_CODE VARCHAR2(6),
	INVENTORY_CHECK_ID VARCHAR2(32),
	INVENTORY_CHECK_RESULT VARCHAR2(2),
	INVENTORY_CHECK_TYPE VARCHAR2(2),
	INVENTORY_CHECK_CATEGORY VARCHAR2(2),
	INVENTORY_CHECK_MOLD VARCHAR2(2),
	CREATE_DATE VARCHAR2(16),
	SPMS_STATUS VARCHAR2(2),
	REQUESTER VARCHAR2(32),
	OPERATOR VARCHAR2(32),
	REVIEWER VARCHAR2(32),
	APPROVER VARCHAR2(32),
	CREATE_TIME DATE default TO_DATE(TO_CHAR(SYS_EXTRACT_UTC(SYSTIMESTAMP), 'yyyy-mm-dd hh24:mi:ss'), 'yyyy-mm-dd hh24:mi:ss'),
	UPDATE_USERNAME VARCHAR2(64),
	UPDATE_TIME DATE,
	JUSTIFICATION VARCHAR2(255),
	REMARKS VARCHAR2(255),
	POSTING_DATE VARCHAR2(16),
	COST_CENTER VARCHAR2(16),
	MOVE_TYPE VARCHAR2(6),
	VOUCHER_TEXT VARCHAR2(255),
	SAP_VOUCHER_NUMBER VARCHAR2(16),
	SAP_VOUCHER_YEAR VARCHAR2(4)
)
/

comment on table INVENTORY_CHECK_ACTION is '仓库盘盈盘亏'
/

comment on column INVENTORY_CHECK_ACTION.UUID is '仓库盘盈盘亏任务UUID'
/

comment on column INVENTORY_CHECK_ACTION.SPMS_ID is '仓库盘盈盘亏单号'
/

comment on column INVENTORY_CHECK_ACTION.INVENTORY_ACTION_TYPE is '盘盈盘亏类别 701-盘盈 702-盘亏'
/

comment on column INVENTORY_CHECK_ACTION.WERKS is '工厂编号'
/

comment on column INVENTORY_CHECK_ACTION.WHS_LOCATION_CODE is '仓库地点代码'
/

comment on column INVENTORY_CHECK_ACTION.INVENTORY_CHECK_ID is '盘点单据号'
/

comment on column INVENTORY_CHECK_ACTION.INVENTORY_CHECK_RESULT is '盘点结果 1-账实相符 2-盘盈 3-盘亏 4-盘盈盘亏'
/

comment on column INVENTORY_CHECK_ACTION.INVENTORY_CHECK_TYPE is '是否杂货 X-备件货 M-贸易货'
/

comment on column INVENTORY_CHECK_ACTION.INVENTORY_CHECK_CATEGORY is '盘点类别 1-日常盘点 2-月度盘点 3-动态盘点 4-其它盘点'
/

comment on column INVENTORY_CHECK_ACTION.INVENTORY_CHECK_MOLD is '盘点类型 1-明盘 2-暗盘'
/

comment on column INVENTORY_CHECK_ACTION.CREATE_DATE is '创建日期'
/

comment on column INVENTORY_CHECK_ACTION.SPMS_STATUS is '任务状态：1-草稿;2-审批中；3-审批通过(待上传SAP)；4-关闭(完成/提交SAP成功)；5-审批不通过；6-提交SAP失败；'
/

comment on column INVENTORY_CHECK_ACTION.REQUESTER is '创建人工号'
/

comment on column INVENTORY_CHECK_ACTION.OPERATOR is '申请人'
/

comment on column INVENTORY_CHECK_ACTION.REVIEWER is '复核人'
/

comment on column INVENTORY_CHECK_ACTION.APPROVER is '审批人'
/

comment on column INVENTORY_CHECK_ACTION.CREATE_TIME is '创建时间'
/

comment on column INVENTORY_CHECK_ACTION.UPDATE_USERNAME is '更新人姓名'
/

comment on column INVENTORY_CHECK_ACTION.UPDATE_TIME is '更新时间'
/

comment on column INVENTORY_CHECK_ACTION.JUSTIFICATION is '申请原因'
/

comment on column INVENTORY_CHECK_ACTION.REMARKS is '备注'
/

comment on column INVENTORY_CHECK_ACTION.POSTING_DATE is '凭证中的过账日期-用户选'
/

comment on column INVENTORY_CHECK_ACTION.COST_CENTER is '成本中心'
/

comment on column INVENTORY_CHECK_ACTION.MOVE_TYPE is '移动类型'
/

comment on column INVENTORY_CHECK_ACTION.VOUCHER_TEXT is '凭证抬头文本'
/

comment on column INVENTORY_CHECK_ACTION.SAP_VOUCHER_NUMBER is 'SAP物料凭证-提交SAP成功后回写'
/

comment on column INVENTORY_CHECK_ACTION.SAP_VOUCHER_YEAR is '会计年份/SAP凭证年份'
/

create trigger TR_INVENTORY_CHECK_ACTION_BEF
	before update
	on INVENTORY_CHECK_ACTION
	for each row
BEGIN
    :new.UPDATE_TIME := TO_DATE(TO_CHAR(SYS_EXTRACT_UTC(SYSTIMESTAMP), 'yyyy-mm-dd hh24:mi:ss'), 'yyyy-mm-dd hh24:mi:ss');
END;
/

create trigger TR_INVENTORY_CHECK_ACTION_ID
	before insert
	on INVENTORY_CHECK_ACTION
	for each row
DECLARE
    V_NUM1 NUMBER := 0;
BEGIN
    SELECT NVL(TO_NUMBER(SUBSTR(MAX(SOT.SPMS_ID), -3, 3)), 0) INTO V_NUM1 FROM INVENTORY_CHECK_ACTION SOT
    WHERE SOT.CREATE_DATE = :NEW.CREATE_DATE
      AND SOT.WERKS = :NEW.WERKS
      AND SOT.WHS_LOCATION_CODE = :NEW.WHS_LOCATION_CODE;
    :NEW.SPMS_ID := :NEW.WERKS||'-'||:NEW.WHS_LOCATION_CODE||'-'||REPLACE(:NEW.CREATE_DATE, '-')||'-'||SUBSTR(1001 + V_NUM1, 2, 4);
END;
/

create table INVENTORY_CHECK_ACTION_DTL
(
	UUID VARCHAR2(32),
	TASK_ROW_ID VARCHAR2(6),
	MATERIAL_NO VARCHAR2(32),
	BOX_NOTE VARCHAR2(50),
	INVENTORY_QUANTITY NUMBER,
	CHECK_QUANTITY NUMBER,
	DISCREPANCY NUMBER,
	BASIC_UNIT VARCHAR2(32),
	STORAGE_UUID VARCHAR2(32),
	REMARKS VARCHAR2(256),
	RELATE_ACCOUNT VARCHAR2(16),
	VOUCHER_ITEM_TEXT VARCHAR2(255)
)
/

comment on table INVENTORY_CHECK_ACTION_DTL is '盘盈盘亏明细表'
/

comment on column INVENTORY_CHECK_ACTION_DTL.UUID is '盘盈盘亏UUID'
/

comment on column INVENTORY_CHECK_ACTION_DTL.TASK_ROW_ID is '盘盈盘亏任务行号'
/

comment on column INVENTORY_CHECK_ACTION_DTL.MATERIAL_NO is '物料编号'
/

comment on column INVENTORY_CHECK_ACTION_DTL.BOX_NOTE is '箱码-仅用于自管仓备件管理'
/

comment on column INVENTORY_CHECK_ACTION_DTL.INVENTORY_QUANTITY is '库存数量'
/

comment on column INVENTORY_CHECK_ACTION_DTL.CHECK_QUANTITY is '实盘数量'
/

comment on column INVENTORY_CHECK_ACTION_DTL.DISCREPANCY is '差异数量'
/

comment on column INVENTORY_CHECK_ACTION_DTL.BASIC_UNIT is '基本单位'
/

comment on column INVENTORY_CHECK_ACTION_DTL.STORAGE_UUID is '储位UUID'
/

comment on column INVENTORY_CHECK_ACTION_DTL.REMARKS is '备注'
/

comment on column INVENTORY_CHECK_ACTION_DTL.RELATE_ACCOUNT is '供应商或债权人的帐号'
/

comment on column INVENTORY_CHECK_ACTION_DTL.VOUCHER_ITEM_TEXT is '项目文本'
/

create table MATERIAL_SAP
(
	MATERIAL_NO VARCHAR2(32) not null
		primary key,
	MATERIAL_ZH_DESC VARCHAR2(128),
	SPMS_MARK VARCHAR2(16) default NULL,
	MATERIAL_EN_DESC VARCHAR2(128),
	PRODUCT_LEVEL_LARGE VARCHAR2(32),
	PRODUCT_LEVEL_LARGE_DESC VARCHAR2(255),
	PRODUCT_LEVEL_MID VARCHAR2(32),
	PRODUCT_LEVEL_MID_DESC VARCHAR2(255),
	PRODUCT_LEVEL_SMALL VARCHAR2(32) default NULL,
	PRODUCT_LEVEL_SMALL_DESC VARCHAR2(225) default NULL,
	PRODUCT_LEVEL VARCHAR2(64),
	PRODUCT_LEVEL_DESC VARCHAR2(128),
	MATERIAL_NAME VARCHAR2(128),
	SPECS VARCHAR2(128) default NULL,
	MATERIAL_MODEL VARCHAR2(64) default NULL,
	SUPPLY_DESC VARCHAR2(128) default NULL,
	BRAND VARCHAR2(64) default NULL,
	SUPPLIER_MATERIAL_NO VARCHAR2(64) default NULL,
	BASIC_UNIT VARCHAR2(16) default NULL,
	PURCHASE_UNIT VARCHAR2(16) default NULL,
	NET_WEIGHT VARCHAR2(32) default NULL,
	GROSS_WEIGHT VARCHAR2(32) default NULL,
	WEIGHT_UNIT VARCHAR2(32) default NULL,
	VOLUME_QTY VARCHAR2(32) default NULL,
	VOLUME_UNIT VARCHAR2(32) default NULL,
	LIGHT_HEAVY_MARK VARCHAR2(16) default NULL,
	IS_DELETE VARCHAR2(16) default NULL,
	MATERIAL_STATUS VARCHAR2(16) default NULL,
	CREATE_DATE VARCHAR2(16),
	SPMS_CREATE_TIME DATE,
	UNIT_SALES VARCHAR2(6),
	NUMERATOR NUMBER,
	DENOMINATOR NUMBER,
	MATERIAL_STANDARD VARCHAR2(32),
	ON_MALL VARCHAR2(3),
	MOVEMENT_TYPE VARCHAR2(6),
	PRODUCT_ADDRESS VARCHAR2(255),
	ORDER_CODE VARCHAR2(128),
	PURCHASE_GROUP VARCHAR2(6),
	PURCHASE_GROUP_DESC VARCHAR2(64),
	RESERVED_FIELD VARCHAR2(32)
)
/

comment on table MATERIAL_SAP is '物料表SAP'
/

comment on column MATERIAL_SAP.MATERIAL_NO is '物料编码'
/

comment on column MATERIAL_SAP.MATERIAL_ZH_DESC is '物料中文描述'
/

comment on column MATERIAL_SAP.SPMS_MARK is 'SPMS标识'
/

comment on column MATERIAL_SAP.MATERIAL_EN_DESC is '物料英文描述'
/

comment on column MATERIAL_SAP.PRODUCT_LEVEL_LARGE is '产品层次 大类'
/

comment on column MATERIAL_SAP.PRODUCT_LEVEL_LARGE_DESC is '产品层次 大类描述'
/

comment on column MATERIAL_SAP.PRODUCT_LEVEL_MID is '产品层次 中级分类'
/

comment on column MATERIAL_SAP.PRODUCT_LEVEL_MID_DESC is '产品层次 中类描述'
/

comment on column MATERIAL_SAP.PRODUCT_LEVEL_SMALL is '产品层次 小类'
/

comment on column MATERIAL_SAP.PRODUCT_LEVEL_SMALL_DESC is '产品层次 小类描述'
/

comment on column MATERIAL_SAP.PRODUCT_LEVEL is '产品层次_组类'
/

comment on column MATERIAL_SAP.PRODUCT_LEVEL_DESC is '产品层次_组类描述'
/

comment on column MATERIAL_SAP.MATERIAL_NAME is '物料名称'
/

comment on column MATERIAL_SAP.SPECS is '规格尺寸'
/

comment on column MATERIAL_SAP.MATERIAL_MODEL is '材质'
/

comment on column MATERIAL_SAP.SUPPLY_DESC is '其他技术补充参数'
/

comment on column MATERIAL_SAP.BRAND is '品牌/厂商'
/

comment on column MATERIAL_SAP.SUPPLIER_MATERIAL_NO is '图号'
/

comment on column MATERIAL_SAP.BASIC_UNIT is '基本计量单位'
/

comment on column MATERIAL_SAP.PURCHASE_UNIT is '采购单位'
/

comment on column MATERIAL_SAP.NET_WEIGHT is '净重'
/

comment on column MATERIAL_SAP.GROSS_WEIGHT is '毛重'
/

comment on column MATERIAL_SAP.WEIGHT_UNIT is '重量单位'
/

comment on column MATERIAL_SAP.VOLUME_QTY is '体积'
/

comment on column MATERIAL_SAP.VOLUME_UNIT is '体积单位'
/

comment on column MATERIAL_SAP.LIGHT_HEAVY_MARK is '轻/重货标识'
/

comment on column MATERIAL_SAP.IS_DELETE is '删除标识'
/

comment on column MATERIAL_SAP.MATERIAL_STATUS is '物料状态（集团层级）'
/

comment on column MATERIAL_SAP.CREATE_DATE is '物料创建日期'
/

comment on column MATERIAL_SAP.SPMS_CREATE_TIME is 'SPMS创建时间'
/

comment on column MATERIAL_SAP.UNIT_SALES is '销售单位'
/

comment on column MATERIAL_SAP.NUMERATOR is '销售单位数量与基本单位数量相比的分母'
/

comment on column MATERIAL_SAP.DENOMINATOR is '销售单位数量与基本单位数量相比的分子'
/

comment on column MATERIAL_SAP.MATERIAL_STANDARD is '物料企标国标/用于判断物料是否可以添加到请购目录'
/

comment on column MATERIAL_SAP.ON_MALL is '是否上架商城'
/

comment on column MATERIAL_SAP.MOVEMENT_TYPE is '默认移动类型'
/

comment on column MATERIAL_SAP.PRODUCT_ADDRESS is '产品地址'
/

comment on column MATERIAL_SAP.ORDER_CODE is '订货编码'
/

comment on column MATERIAL_SAP.PURCHASE_GROUP is '采购组'
/

comment on column MATERIAL_SAP.PURCHASE_GROUP_DESC is '采购组描述'
/

comment on column MATERIAL_SAP.RESERVED_FIELD is '第三方平台(预留字段)'
/

create index INDEX_MATERIAL_SAP_EN_DESC
	on MATERIAL_SAP (MATERIAL_EN_DESC)
/

create index INDEX_MATERIAL_SAP_ZH_DESC
	on MATERIAL_SAP (MATERIAL_ZH_DESC)
/

create index INDEX_SPMS_MARK
	on MATERIAL_SAP (SPMS_MARK)
/

create trigger TR_MATERIAL_SAP
	before insert
	on MATERIAL_SAP
	for each row
DECLARE
    NUM NUMBER := 0;
BEGIN
    SELECT COUNT(*) INTO NUM FROM MATERIAL_SAP MS WHERE MS.MATERIAL_NO = :NEW.MATERIAL_NO;
    :NEW.SPMS_CREATE_TIME := TO_DATE(TO_CHAR(SYSDATE, 'yyyy-mm-dd hh24:mi:ss'), 'yyyy-mm-dd hh24:mi:ss');
    if NUM = 1 THEN
        DELETE FROM MATERIAL_SAP M WHERE M.MATERIAL_NO = :NEW.MATERIAL_NO;
    end if;
end;
/

create table MATERIAL_APPLICATION
(
	APPLICATION_UUID VARCHAR2(32) not null
		primary key,
	APPLICATION_ID VARCHAR2(32),
	USER_ID VARCHAR2(60),
	WERKS VARCHAR2(10),
	WORKSHOP_CODE VARCHAR2(10),
	APPLICATION_TYPE VARCHAR2(2),
	SPMS_STATUS VARCHAR2(2),
	REMARKS VARCHAR2(255),
	APPLICATION_DATE VARCHAR2(16),
	CREATE_TIME DATE default TO_DATE(TO_CHAR(SYS_EXTRACT_UTC(SYSTIMESTAMP), 'yyyy-mm-dd hh24:mi:ss'), 'yyyy-mm-dd hh24:mi:ss'),
	UPDATE_TIME DATE default TO_DATE(TO_CHAR(SYS_EXTRACT_UTC(SYSTIMESTAMP), 'yyyy-mm-dd hh24:mi:ss'), 'yyyy-mm-dd hh24:mi:ss'),
	CPX VARCHAR2(2),
	OA_BIANHAO VARCHAR2(16),
	OA_ORDER_NO VARCHAR2(32),
	OA_APPROVAL_DATE VARCHAR2(16),
	REVIEWER_WORK_CODE VARCHAR2(10),
	OA_SUBMIT_DATE DATE
)
/

comment on table MATERIAL_APPLICATION is '物料申请单表表头'
/

comment on column MATERIAL_APPLICATION.APPLICATION_UUID is '物料申请单uuid'
/

comment on column MATERIAL_APPLICATION.APPLICATION_ID is '物料申请单号（工厂+车间+序列号）'
/

comment on column MATERIAL_APPLICATION.USER_ID is '用户工号'
/

comment on column MATERIAL_APPLICATION.WERKS is '工厂代码'
/

comment on column MATERIAL_APPLICATION.WORKSHOP_CODE is '车间代码（申请部门）'
/

comment on column MATERIAL_APPLICATION.APPLICATION_TYPE is '申请类型（1-增补物料；2-修改物料信息）'
/

comment on column MATERIAL_APPLICATION.SPMS_STATUS is '物料申请单状态（0-删除；1-保存；2-提交OA成功；3-提交OA失败；4-OA审批通过；5-OA审批不通过；6-完成）'
/

comment on column MATERIAL_APPLICATION.REMARKS is '备注信息'
/

comment on column MATERIAL_APPLICATION.APPLICATION_DATE is '申请日期'
/

comment on column MATERIAL_APPLICATION.CREATE_TIME is '创建时间'
/

comment on column MATERIAL_APPLICATION.UPDATE_TIME is '更新时间，自动生成'
/

comment on column MATERIAL_APPLICATION.CPX is '备件类别（0建材专属；1快消专属；2五金专属；3通用专属；4非备件类）'
/

comment on column MATERIAL_APPLICATION.OA_BIANHAO is '提交OA返回的编号'
/

comment on column MATERIAL_APPLICATION.OA_ORDER_NO is 'OA流水号'
/

comment on column MATERIAL_APPLICATION.OA_APPROVAL_DATE is 'OA审批通过日期yyyy-MM-dd'
/

comment on column MATERIAL_APPLICATION.REVIEWER_WORK_CODE is '物料工程师ID'
/

comment on column MATERIAL_APPLICATION.OA_SUBMIT_DATE is 'OA提交日期'
/

create trigger TR_MATERIAL_APPLICATION_BEF
	before insert
	on MATERIAL_APPLICATION
	for each row
DECLARE
    V_NUM1 NUMBER := 0;
BEGIN
    SELECT NVL(TO_NUMBER(SUBSTR(MAX(MA.APPLICATION_ID), -5, 5)), 0) INTO V_NUM1 FROM MATERIAL_APPLICATION MA
    WHERE MA.WERKS = :NEW.WERKS AND MA.WORKSHOP_CODE = :NEW.WORKSHOP_CODE;
    :NEW.APPLICATION_ID := :NEW.WERKS||'-'||:NEW.WORKSHOP_CODE||'-'||SUBSTR(100001 + V_NUM1, 2, 6);
END;
/

create trigger TR_MATERIAL_APPLICATION_BEF1
	before update
	on MATERIAL_APPLICATION
	for each row
BEGIN
    :NEW.UPDATE_TIME := TO_DATE(TO_CHAR(SYS_EXTRACT_UTC(SYSTIMESTAMP), 'yyyy-mm-dd hh24:mi:ss'), 'yyyy-mm-dd hh24:mi:ss');
END;
/

create table MATERIAL_APPLICATION_DTL
(
	APPLICATION_UUID VARCHAR2(32),
	APPLICATION_ITEM VARCHAR2(6),
	MATERIAL_EN_DESC VARCHAR2(255),
	SPECS VARCHAR2(255),
	BASIC_UNIT VARCHAR2(6),
	MATERIAL_MODEL VARCHAR2(255),
	SUPPLIER_MATERIAL_NO VARCHAR2(255),
	SUPPLY_DESC VARCHAR2(255),
	RELATE_EQUIPMENT VARCHAR2(255),
	EQUIPMENT_SPEC VARCHAR2(128),
	EQUIPEMNT_SUPPLIER VARCHAR2(128),
	EQUIPMENT_BRAND VARCHAR2(64),
	PRODUCT_LEVEL_LARGE_DESC VARCHAR2(128),
	PRODUCT_LEVEL_MID_DESC VARCHAR2(128),
	PRODUCT_LEVEL_SMALL_DESC VARCHAR2(128),
	PICTURE VARCHAR2(1000),
	REMARKS VARCHAR2(255),
	AUTO_ID NUMBER not null,
	MATERIAL_NO VARCHAR2(40),
	PURCHASE_USERNAME VARCHAR2(64),
	SPARE_FIELD VARCHAR2(64),
	THIRD_PARTY_PLATFORM VARCHAR2(64),
	LINK VARCHAR2(1000),
	SKU VARCHAR2(64)
)
/

comment on table MATERIAL_APPLICATION_DTL is '物料申请单明细表'
/

comment on column MATERIAL_APPLICATION_DTL.APPLICATION_UUID is '物料申请单uuid'
/

comment on column MATERIAL_APPLICATION_DTL.APPLICATION_ITEM is '物料申请单行号'
/

comment on column MATERIAL_APPLICATION_DTL.MATERIAL_EN_DESC is '物料名称/描述'
/

comment on column MATERIAL_APPLICATION_DTL.SPECS is '规格型号'
/

comment on column MATERIAL_APPLICATION_DTL.BASIC_UNIT is '基本单位'
/

comment on column MATERIAL_APPLICATION_DTL.MATERIAL_MODEL is '材质'
/

comment on column MATERIAL_APPLICATION_DTL.SUPPLIER_MATERIAL_NO is '图号/订货号'
/

comment on column MATERIAL_APPLICATION_DTL.SUPPLY_DESC is '其他技术补充参数'
/

comment on column MATERIAL_APPLICATION_DTL.RELATE_EQUIPMENT is '配套设备名称'
/

comment on column MATERIAL_APPLICATION_DTL.EQUIPMENT_SPEC is '设备型号'
/

comment on column MATERIAL_APPLICATION_DTL.EQUIPEMNT_SUPPLIER is '设备供应商'
/

comment on column MATERIAL_APPLICATION_DTL.EQUIPMENT_BRAND is '设备品牌'
/

comment on column MATERIAL_APPLICATION_DTL.PRODUCT_LEVEL_LARGE_DESC is '备件大类'
/

comment on column MATERIAL_APPLICATION_DTL.PRODUCT_LEVEL_MID_DESC is '备件次级分类'
/

comment on column MATERIAL_APPLICATION_DTL.PRODUCT_LEVEL_SMALL_DESC is '备件小类'
/

comment on column MATERIAL_APPLICATION_DTL.PICTURE is '备件图片'
/

comment on column MATERIAL_APPLICATION_DTL.REMARKS is '备注信息'
/

comment on column MATERIAL_APPLICATION_DTL.AUTO_ID is '物料申请单明细自增ID'
/

comment on column MATERIAL_APPLICATION_DTL.MATERIAL_NO is '物料号'
/

comment on column MATERIAL_APPLICATION_DTL.PURCHASE_USERNAME is '采购员（绑定采购组）'
/

comment on column MATERIAL_APPLICATION_DTL.SPARE_FIELD is '备用字段1'
/

comment on column MATERIAL_APPLICATION_DTL.THIRD_PARTY_PLATFORM is '第三方平台'
/

comment on column MATERIAL_APPLICATION_DTL.LINK is '产品网址'
/

comment on column MATERIAL_APPLICATION_DTL.SKU is '订货编码'
/

create unique index MATERIAL_APPLICATION_DTL_AUTO_ID_UINDEX
	on MATERIAL_APPLICATION_DTL (AUTO_ID)
/

alter table MATERIAL_APPLICATION_DTL
	add constraint MATERIAL_APPLICATION_DTL_PK
		primary key (AUTO_ID)
/

create trigger TR_MATERIAL_APPLICATION_DTL_BEF
	before insert
	on MATERIAL_APPLICATION_DTL
	for each row
DECLARE
    V_NUM1 NUMBER := 0;
BEGIN
    SELECT NVL(TO_NUMBER(MAX(MAD.AUTO_ID)), 0) INTO V_NUM1 FROM MATERIAL_APPLICATION_DTL MAD;
    :NEW.AUTO_ID := V_NUM1 + 1;
END;
/

create table WHS_MOVE
(
	WHS_MOVE_UUID VARCHAR2(32) not null
		constraint WHS_MOVE_PK
			primary key,
	WHS_MOVE_NO VARCHAR2(32),
	MOVE_TYPE VARCHAR2(6),
	GOODS_TYPE VARCHAR2(2),
	WERKS_MOVE_OUT VARCHAR2(6),
	WHS_LOCATION_CODE_MOVE_OUT VARCHAR2(6),
	MOVE_OUT_REMARK VARCHAR2(255),
	WERKS_MOVE_IN VARCHAR2(6),
	WHS_LOCATION_CODE_MOVE_IN VARCHAR2(6),
	MOVE_IN_REMARK VARCHAR2(255),
	CREATE_DATE VARCHAR2(16),
	POSTING_DATE VARCHAR2(16),
	SAP_VOUCHER VARCHAR2(64),
	CREATE_USERID VARCHAR2(10),
	MOVE_OUT_USERID VARCHAR2(10),
	MOVE_IN_USERID VARCHAR2(10),
	SPMS_STATUS VARCHAR2(2),
	CREATE_TIME DATE default TO_DATE(TO_CHAR(SYS_EXTRACT_UTC(SYSTIMESTAMP), 'yyyy-mm-dd hh24:mi:ss'), 'yyyy-mm-dd hh24:mi:ss'),
	REMARK VARCHAR2(255),
	SAP_VOUCHER_YEAR VARCHAR2(16)
)
/

comment on table WHS_MOVE is '移仓任务表头'
/

comment on column WHS_MOVE.WHS_MOVE_UUID is '移仓任务uuid'
/

comment on column WHS_MOVE.WHS_MOVE_NO is '移仓任务单号'
/

comment on column WHS_MOVE.MOVE_TYPE is '移动类型'
/

comment on column WHS_MOVE.GOODS_TYPE is '货物类型'
/

comment on column WHS_MOVE.WERKS_MOVE_OUT is '移出工厂代码'
/

comment on column WHS_MOVE.WHS_LOCATION_CODE_MOVE_OUT is '移出仓库代码'
/

comment on column WHS_MOVE.MOVE_OUT_REMARK is '移出备注'
/

comment on column WHS_MOVE.WERKS_MOVE_IN is '移入工厂代码'
/

comment on column WHS_MOVE.WHS_LOCATION_CODE_MOVE_IN is '移入仓库代码'
/

comment on column WHS_MOVE.MOVE_IN_REMARK is '移入备注'
/

comment on column WHS_MOVE.CREATE_DATE is '创建日期'
/

comment on column WHS_MOVE.POSTING_DATE is '过账日期'
/

comment on column WHS_MOVE.SAP_VOUCHER is 'SAP凭证'
/

comment on column WHS_MOVE.CREATE_USERID is '创建人工号'
/

comment on column WHS_MOVE.MOVE_OUT_USERID is '移出执行人工号'
/

comment on column WHS_MOVE.MOVE_IN_USERID is '移入执行人工号'
/

comment on column WHS_MOVE.SPMS_STATUS is '单据状态(0删除;1草稿;2移出可执行;3移出提交(移入可执行);4移入提交(待上传SAP);5提交SAP成功;6完成(计算库存);'
/

comment on column WHS_MOVE.CREATE_TIME is '创建时间'
/

comment on column WHS_MOVE.REMARK is '备注字段'
/

comment on column WHS_MOVE.SAP_VOUCHER_YEAR is 'SAP凭证年份'
/

create trigger TR_WHS_MOVE_BEF
	before insert
	on WHS_MOVE
	for each row
DECLARE
    V_NUM1 NUMBER := 0;
BEGIN
    SELECT NVL(TO_NUMBER(SUBSTR(MAX(WM.WHS_MOVE_NO), -3, 3)), 0) INTO V_NUM1 FROM WHS_MOVE WM
    WHERE WM.CREATE_DATE = :NEW.CREATE_DATE
      AND WM.WERKS_MOVE_OUT = :NEW.WERKS_MOVE_OUT;
    :NEW.WHS_MOVE_NO := :NEW.WERKS_MOVE_OUT||'-'||REPLACE(:NEW.CREATE_DATE, '-')||'-'|| SUBSTR(1001 + V_NUM1, 2, 4);
END;
/

create table WHS_MOVE_DTL
(
	WHS_MOVE_UUID VARCHAR2(32),
	AUTO_ID NUMBER not null
		constraint WHS_MOVE_DTL_PK
			primary key,
	ITEM_NO NUMBER,
	MATERIAL_NO VARCHAR2(40),
	BASIC_UNIT VARCHAR2(6),
	BOX_NOTE VARCHAR2(50),
	PLANNED_OUT_QTY NUMBER,
	PLANNED_OUT_STORAGE VARCHAR2(4000),
	ACTUAL_OUT_QTY NUMBER,
	MOVE_OUT_STORAGE VARCHAR2(4000),
	MOVE_OUT_REMARK VARCHAR2(255),
	ACTUAL_IN_QTY NUMBER,
	MOVE_IN_STORAGE VARCHAR2(4000),
	MOVE_IN_REMARK VARCHAR2(255),
	SUPPLIER_NO VARCHAR2(64),
	COST_CENTER VARCHAR2(16),
	REMARK VARCHAR2(255)
)
/

comment on table WHS_MOVE_DTL is '移仓任务明细'
/

comment on column WHS_MOVE_DTL.WHS_MOVE_UUID is '移仓任务uuid'
/

comment on column WHS_MOVE_DTL.AUTO_ID is '自增ID'
/

comment on column WHS_MOVE_DTL.ITEM_NO is '行号'
/

comment on column WHS_MOVE_DTL.MATERIAL_NO is '物料编号'
/

comment on column WHS_MOVE_DTL.BASIC_UNIT is '单位'
/

comment on column WHS_MOVE_DTL.BOX_NOTE is '箱码'
/

comment on column WHS_MOVE_DTL.PLANNED_OUT_QTY is '计划移出数量'
/

comment on column WHS_MOVE_DTL.PLANNED_OUT_STORAGE is '计划移出储位信息'
/

comment on column WHS_MOVE_DTL.ACTUAL_OUT_QTY is '实际移出数量'
/

comment on column WHS_MOVE_DTL.MOVE_OUT_STORAGE is '实际移出储位信息'
/

comment on column WHS_MOVE_DTL.MOVE_OUT_REMARK is '移出备注'
/

comment on column WHS_MOVE_DTL.ACTUAL_IN_QTY is '实际移入数量'
/

comment on column WHS_MOVE_DTL.MOVE_IN_STORAGE is '实际移入储位信息'
/

comment on column WHS_MOVE_DTL.MOVE_IN_REMARK is '移入备注'
/

comment on column WHS_MOVE_DTL.SUPPLIER_NO is '供应商账号'
/

comment on column WHS_MOVE_DTL.COST_CENTER is '成本中心'
/

comment on column WHS_MOVE_DTL.REMARK is '备注信息'
/

create trigger TR_WHS_MOVE_DTL_BEF
	before insert
	on WHS_MOVE_DTL
	for each row
DECLARE
    V_NUM1 NUMBER := 0;
BEGIN
    SELECT NVL(TO_NUMBER(MAX(WDM.AUTO_ID)), 0) INTO V_NUM1 FROM WHS_MOVE_DTL WDM;
    :NEW.AUTO_ID := V_NUM1 + 1;
END;
/

create table WHS_OTHER_TASK
(
	UUID VARCHAR2(32) not null
		constraint WHS_OTHER_TASK_PK
			primary key,
	SPMS_ID VARCHAR2(32),
	MOVEMENT_TYPE VARCHAR2(32),
	WERKS VARCHAR2(6),
	WHS_LOCATION_CODE VARCHAR2(6),
	SAP_VOUCHER_NUMBER VARCHAR2(32),
	SAP_VOUCHER_YEAR VARCHAR2(8),
	DOCUMENT_DATE VARCHAR2(10),
	POSTING_DATE VARCHAR2(10),
	CREATE_USERID VARCHAR2(20),
	SAP_PO VARCHAR2(32),
	SAP_ORDER_TYPE VARCHAR2(32),
	PURCHASE_GROUP VARCHAR2(6),
	PURCHASE_GROUP_NAME VARCHAR2(64),
	RELATED_VOUCHER_NUMBER VARCHAR2(32),
	RELATED_VOUCHER_YEAR VARCHAR2(8),
	RELATED_DOCUMENT_DATE VARCHAR2(10),
	RELATED_POSTING_DATE VARCHAR2(10),
	COST_CENTER VARCHAR2(16),
	PACKAGE_LIST VARCHAR2(16),
	GOODS_TYPE VARCHAR2(30),
	TASK_STATUS VARCHAR2(4),
	CREATE_DATE VARCHAR2(10),
	CREATE_TIME DATE default TO_DATE(TO_CHAR(SYS_EXTRACT_UTC(SYSTIMESTAMP), 'yyyy-mm-dd hh24:mi:ss'), 'yyyy-mm-dd hh24:mi:ss'),
	PACKAGE_NOTE VARCHAR2(50),
	DELIVERY_NOTE_SRM VARCHAR2(35),
	RELATED_TASK_ID VARCHAR2(64),
	UPDATE_TIME DATE,
	REMARKS VARCHAR2(255),
	DELIVERY_NOTE_SAP VARCHAR2(32),
	SAP_MOVEMENT_TYPE VARCHAR2(16),
	WERKS_MOVE_OUT VARCHAR2(6),
	WERKS_MOVE_IN VARCHAR2(6),
	WHS_LOCATION_CODE_MOVE_OUT VARCHAR2(6),
	WHS_LOCATION_CODE_MOVE_IN VARCHAR2(6),
	MOVE_OUT_REMARK VARCHAR2(255),
	MOVE_IN_REMARK VARCHAR2(255),
	DATE_OF_LOADING VARCHAR2(10),
	CABINET_NUMBER VARCHAR2(16),
	CABINET_SERIAL_NUMBER VARCHAR2(2),
	CABINET_CHECK_RESULT VARCHAR2(128),
	ACTUAL_LOADING_DATE VARCHAR2(16),
	MONITOR_LOADING VARCHAR2(60),
	LEAD_SEAL VARCHAR2(32),
	EMPTY_CABINET_WEIGHT NUMBER(13,3) default 0.000,
	CANCEL_REMARK VARCHAR2(255),
	CANCEL_VOUCHER_NUMBER VARCHAR2(32),
	CANCEL_VOUCHER_YEAR VARCHAR2(8),
	CANCEL_DATE VARCHAR2(12),
	OPERATE_USER VARCHAR2(255),
	JUSTIFICATION VARCHAR2(8)
)
/

comment on table WHS_OTHER_TASK is '异常出入库任务表'
/

comment on column WHS_OTHER_TASK.UUID is '异常出入库任务表主键UUID'
/

comment on column WHS_OTHER_TASK.SPMS_ID is '异常出入库SPMS单号'
/

comment on column WHS_OTHER_TASK.MOVEMENT_TYPE is '移动类型 卸货入库-311 赠品入库-511(对冲512) 串货调整-309(对冲310）报废-551(冲销552)
样品领用-Z09(冲销Z10) 仓库工具领用-Z21(冲销Z22) 其他公司领用-Z23(冲销Z24) 退货出库-161(冲销162) 委外加工发货出库-541(冲销542)'
/

comment on column WHS_OTHER_TASK.WERKS is '工厂代码'
/

comment on column WHS_OTHER_TASK.WHS_LOCATION_CODE is '仓库代码'
/

comment on column WHS_OTHER_TASK.SAP_VOUCHER_NUMBER is 'SAP凭证号'
/

comment on column WHS_OTHER_TASK.SAP_VOUCHER_YEAR is 'SAP凭证年份'
/

comment on column WHS_OTHER_TASK.DOCUMENT_DATE is 'SAP凭证日期'
/

comment on column WHS_OTHER_TASK.POSTING_DATE is '过帐日期'
/

comment on column WHS_OTHER_TASK.CREATE_USERID is '操作员ID'
/

comment on column WHS_OTHER_TASK.SAP_PO is 'SAP采购订单号'
/

comment on column WHS_OTHER_TASK.SAP_ORDER_TYPE is 'SAP采购订单类型'
/

comment on column WHS_OTHER_TASK.PURCHASE_GROUP is '采购组'
/

comment on column WHS_OTHER_TASK.PURCHASE_GROUP_NAME is '采购组名称'
/

comment on column WHS_OTHER_TASK.RELATED_VOUCHER_NUMBER is '关联凭证号'
/

comment on column WHS_OTHER_TASK.RELATED_VOUCHER_YEAR is '关联凭证年度'
/

comment on column WHS_OTHER_TASK.RELATED_DOCUMENT_DATE is '关联凭证日期'
/

comment on column WHS_OTHER_TASK.RELATED_POSTING_DATE is '关联过帐日期'
/

comment on column WHS_OTHER_TASK.COST_CENTER is '成本中心'
/

comment on column WHS_OTHER_TASK.PACKAGE_LIST is '装箱单号'
/

comment on column WHS_OTHER_TASK.GOODS_TYPE is '货物类型 X-备件 M-贸易'
/

comment on column WHS_OTHER_TASK.TASK_STATUS is '单据状态 0-作废 1-草稿 2-操作中 3-待提交SAP 4-提交SAP成功 5-提交SAP失败 6-完成 7-已撤销'
/

comment on column WHS_OTHER_TASK.CREATE_DATE is '创建日期'
/

comment on column WHS_OTHER_TASK.CREATE_TIME is '创建时间，系统产生'
/

comment on column WHS_OTHER_TASK.PACKAGE_NOTE is '包装单号'
/

comment on column WHS_OTHER_TASK.DELIVERY_NOTE_SRM is 'SAP交货单号'
/

comment on column WHS_OTHER_TASK.RELATED_TASK_ID is '关联单据号码'
/

comment on column WHS_OTHER_TASK.UPDATE_TIME is '列新时间'
/

comment on column WHS_OTHER_TASK.REMARKS is '备注'
/

comment on column WHS_OTHER_TASK.DELIVERY_NOTE_SAP is 'SAP 内向交货单号'
/

comment on column WHS_OTHER_TASK.SAP_MOVEMENT_TYPE is 'SAP移动类型'
/

comment on column WHS_OTHER_TASK.WERKS_MOVE_OUT is '移出工厂代码'
/

comment on column WHS_OTHER_TASK.WERKS_MOVE_IN is '移入工厂代码'
/

comment on column WHS_OTHER_TASK.WHS_LOCATION_CODE_MOVE_OUT is '移出仓库代码'
/

comment on column WHS_OTHER_TASK.WHS_LOCATION_CODE_MOVE_IN is '移入仓库代码'
/

comment on column WHS_OTHER_TASK.MOVE_OUT_REMARK is '移出备注'
/

comment on column WHS_OTHER_TASK.MOVE_IN_REMARK is '移入备注'
/

comment on column WHS_OTHER_TASK.DATE_OF_LOADING is '可装柜日期'
/

comment on column WHS_OTHER_TASK.CABINET_NUMBER is '柜号'
/

comment on column WHS_OTHER_TASK.CABINET_SERIAL_NUMBER is '柜序号'
/

comment on column WHS_OTHER_TASK.CABINET_CHECK_RESULT is '货柜检验结果'
/

comment on column WHS_OTHER_TASK.ACTUAL_LOADING_DATE is '实际装柜日期'
/

comment on column WHS_OTHER_TASK.MONITOR_LOADING is '监装人员'
/

comment on column WHS_OTHER_TASK.LEAD_SEAL is '铅封号'
/

comment on column WHS_OTHER_TASK.EMPTY_CABINET_WEIGHT is '空柜重量（KG）'
/

comment on column WHS_OTHER_TASK.CANCEL_REMARK is '撤销备注信息'
/

comment on column WHS_OTHER_TASK.CANCEL_VOUCHER_NUMBER is 'SAP撤销凭证号'
/

comment on column WHS_OTHER_TASK.CANCEL_VOUCHER_YEAR is 'SAP撤销凭证年份'
/

comment on column WHS_OTHER_TASK.CANCEL_DATE is 'SAP撤销日期(手输默认过账日期)'
/

comment on column WHS_OTHER_TASK.OPERATE_USER is '操作员/现场搬运工计费'
/

comment on column WHS_OTHER_TASK.JUSTIFICATION is '移动原因当MSEG-BWART=511，需用户选择MSEG-GRUND字段值，值为0001来料破损、0002在库破损、0003其它问题、0004出货破损、1001来货报废
1002在库报废、1003其他报废'
/

create trigger TR_WHS_OTHER_TASK_BEF
	before insert
	on WHS_OTHER_TASK
	for each row
DECLARE
    V_NUM1 NUMBER := 0;
BEGIN
    SELECT NVL(TO_NUMBER(SUBSTR(MAX(WO.SPMS_ID), -3, 3)), 0) INTO V_NUM1 FROM WHS_OTHER_TASK WO
    WHERE WO.CREATE_DATE = :NEW.CREATE_DATE
      AND WO.WERKS = :NEW.WERKS
      AND WO.WHS_LOCATION_CODE = :NEW.WHS_LOCATION_CODE;
    :NEW.SPMS_ID := :NEW.WERKS||'-'||:NEW.WHS_LOCATION_CODE||'-'||REPLACE(:NEW.CREATE_DATE, '-')||'-'|| SUBSTR(1001 + V_NUM1, 2, 4);
END;
/

create table WHS_OTHER_DTL
(
	WHS_OTHER_UUID VARCHAR2(32),
	AUTO_ID NUMBER not null
		constraint WHS_OTHER_DTL_PK
			primary key,
	ROW_ID NUMBER,
	BOX_NOTE VARCHAR2(50),
	MATERIAL_NO VARCHAR2(40),
	OPERATE_QTY NUMBER,
	BASIC_UNIT VARCHAR2(6),
	PACKAGE_UNIT VARCHAR2(16),
	PACKAGE_LIST VARCHAR2(20),
	PACKAGE_ROW_ID NUMBER,
	PACKAGE_QTY NUMBER,
	COST_CENTER VARCHAR2(16),
	SUPPLIER_NO VARCHAR2(16),
	SUPPLIER_DESC VARCHAR2(128),
	MATERIAL_NO_OUT VARCHAR2(40),
	QTY_OUT NUMBER,
	MATERIAL_NO_IN VARCHAR2(40),
	QTY_IN NUMBER,
	WERKS VARCHAR2(6),
	WHS_LOCATION_CODE VARCHAR2(6),
	ROW_REMARKS VARCHAR2(255),
	TEMP_STORAGE VARCHAR2(4000),
	DELIVERY_NOTE_SAP VARCHAR2(10),
	DELIVERY_NOTE_SAP_ITEM NUMBER,
	PURCHASE_GROUP VARCHAR2(6),
	PURCHASE_GROUP_NAME VARCHAR2(64),
	DELIVERY_QTY NUMBER(13,3),
	DELIVERY_UNIT VARCHAR2(3),
	RECEIVED_QTY NUMBER(13,3),
	OVER_DELIVERY_LIMIT NUMBER,
	UNDER_DELIVERY_LIMIT NUMBER,
	UNLIMITED VARCHAR2(2),
	WERKS_XQ VARCHAR2(6),
	QTY_SALES NUMBER(13,3),
	UNIT_SALES VARCHAR2(3),
	CABINET_SERIAL_NUMBER VARCHAR2(2),
	GOODS_TYPE VARCHAR2(30),
	RECEIVING_WERKS VARCHAR2(6),
	ACTUAL_RECEIVING_WERKS VARCHAR2(6),
	CABINET_NUMBER VARCHAR2(16),
	LEAD_SEAL VARCHAR2(16),
	SHIPMENT_CATEGORY VARCHAR2(64),
	PLANNED_PACKING_QTY NUMBER(17,3),
	ITEM VARCHAR2(6),
	DATE_OF_LOADING VARCHAR2(10),
	LOADING_WERKS VARCHAR2(6),
	LOADING_WHS_LOCATION_CODE VARCHAR2(4),
	PLANNED_PACKING_NUMBER NUMBER(17,3),
	PACKING_UNIT VARCHAR2(3),
	CABINET_WEIGHT NUMBER(17,6),
	WEIGHT_UNIT VARCHAR2(6),
	CABINET_VOLUME NUMBER(17,6),
	VOLUME_UNIT VARCHAR2(6),
	TRAILER_LINE VARCHAR2(6),
	TRAILER_LINE_DESC VARCHAR2(40),
	SPMS_STATUS VARCHAR2(2) default 0,
	UNPLANNED VARCHAR2(2),
	ACTUAL_OPERATE_QTY NUMBER(17,3),
	CONTAINER_BRAND VARCHAR2(64),
	COLOR_NUMBER VARCHAR2(32),
	THICKNESS NUMBER(7,3),
	ACTUAL_AVG_GROSS_WEIGHT NUMBER(7,3),
	ACTUAL_PACKING_NUMBER NUMBER(17,3),
	CUSTOMS_DECLARATION_NUMBER NUMBER(17,3),
	PACKING_SPEC VARCHAR2(128),
	LOADING_TYPE VARCHAR2(128),
	STORAGE_OUT VARCHAR2(4000),
	STORAGE_IN VARCHAR2(4000)
)
/

comment on table WHS_OTHER_DTL is '异常出入库明细表'
/

comment on column WHS_OTHER_DTL.WHS_OTHER_UUID is '异常出入库主键'
/

comment on column WHS_OTHER_DTL.AUTO_ID is '自增ID'
/

comment on column WHS_OTHER_DTL.ROW_ID is '行ID'
/

comment on column WHS_OTHER_DTL.BOX_NOTE is '箱码'
/

comment on column WHS_OTHER_DTL.MATERIAL_NO is '物料编码'
/

comment on column WHS_OTHER_DTL.OPERATE_QTY is '出入库操作数量'
/

comment on column WHS_OTHER_DTL.BASIC_UNIT is '基本单位'
/

comment on column WHS_OTHER_DTL.PACKAGE_UNIT is '包装单位'
/

comment on column WHS_OTHER_DTL.PACKAGE_LIST is '装箱单号'
/

comment on column WHS_OTHER_DTL.PACKAGE_ROW_ID is '装箱单行号'
/

comment on column WHS_OTHER_DTL.PACKAGE_QTY is '包装数量'
/

comment on column WHS_OTHER_DTL.COST_CENTER is '成本中心'
/

comment on column WHS_OTHER_DTL.SUPPLIER_NO is '供应商代码'
/

comment on column WHS_OTHER_DTL.SUPPLIER_DESC is '供应商描述'
/

comment on column WHS_OTHER_DTL.MATERIAL_NO_OUT is '出库物料编码'
/

comment on column WHS_OTHER_DTL.QTY_OUT is '出库数量'
/

comment on column WHS_OTHER_DTL.MATERIAL_NO_IN is '入库物料编码'
/

comment on column WHS_OTHER_DTL.QTY_IN is '入库数量'
/

comment on column WHS_OTHER_DTL.WERKS is '工厂'
/

comment on column WHS_OTHER_DTL.WHS_LOCATION_CODE is '仓库代码'
/

comment on column WHS_OTHER_DTL.ROW_REMARKS is '行备注'
/

comment on column WHS_OTHER_DTL.TEMP_STORAGE is '储位信息'
/

comment on column WHS_OTHER_DTL.DELIVERY_NOTE_SAP is 'SAP交货单号'
/

comment on column WHS_OTHER_DTL.DELIVERY_NOTE_SAP_ITEM is 'SAP交货单行号'
/

comment on column WHS_OTHER_DTL.PURCHASE_GROUP is '采购组'
/

comment on column WHS_OTHER_DTL.PURCHASE_GROUP_NAME is '采购组名称'
/

comment on column WHS_OTHER_DTL.DELIVERY_QTY is '交货数量'
/

comment on column WHS_OTHER_DTL.DELIVERY_UNIT is '销售单位'
/

comment on column WHS_OTHER_DTL.RECEIVED_QTY is '实际收货数量'
/

comment on column WHS_OTHER_DTL.OVER_DELIVERY_LIMIT is '过量交货限度'
/

comment on column WHS_OTHER_DTL.UNDER_DELIVERY_LIMIT is '交货不足限度'
/

comment on column WHS_OTHER_DTL.UNLIMITED is '无限制'
/

comment on column WHS_OTHER_DTL.WERKS_XQ is '需求工厂'
/

comment on column WHS_OTHER_DTL.QTY_SALES is '销售数量'
/

comment on column WHS_OTHER_DTL.UNIT_SALES is '销售单位'
/

comment on column WHS_OTHER_DTL.CABINET_SERIAL_NUMBER is '柜序号'
/

comment on column WHS_OTHER_DTL.GOODS_TYPE is '货物类型/产品层次'
/

comment on column WHS_OTHER_DTL.RECEIVING_WERKS is '收货工厂'
/

comment on column WHS_OTHER_DTL.ACTUAL_RECEIVING_WERKS is '实际收货工厂'
/

comment on column WHS_OTHER_DTL.CABINET_NUMBER is '柜号'
/

comment on column WHS_OTHER_DTL.LEAD_SEAL is '铅封号'
/

comment on column WHS_OTHER_DTL.SHIPMENT_CATEGORY is '出货品类'
/

comment on column WHS_OTHER_DTL.PLANNED_PACKING_QTY is '计划装箱数量(基本单位)'
/

comment on column WHS_OTHER_DTL.ITEM is '装箱单行号'
/

comment on column WHS_OTHER_DTL.DATE_OF_LOADING is '可装柜日期'
/

comment on column WHS_OTHER_DTL.LOADING_WERKS is '装柜工厂代码'
/

comment on column WHS_OTHER_DTL.LOADING_WHS_LOCATION_CODE is '装柜仓库代码'
/

comment on column WHS_OTHER_DTL.PLANNED_PACKING_NUMBER is '计划装箱件数'
/

comment on column WHS_OTHER_DTL.PACKING_UNIT is '包装单位'
/

comment on column WHS_OTHER_DTL.CABINET_WEIGHT is '柜重量'
/

comment on column WHS_OTHER_DTL.WEIGHT_UNIT is '重量单位'
/

comment on column WHS_OTHER_DTL.CABINET_VOLUME is '柜体积'
/

comment on column WHS_OTHER_DTL.VOLUME_UNIT is '体积单位'
/

comment on column WHS_OTHER_DTL.TRAILER_LINE is '出发国拖车线路'
/

comment on column WHS_OTHER_DTL.TRAILER_LINE_DESC is '出发国拖车线路名称'
/

comment on column WHS_OTHER_DTL.SPMS_STATUS is '装箱单行项目在SPMS状态'
/

comment on column WHS_OTHER_DTL.UNPLANNED is '是否为计划外的明细。默认空，额外出库的贸易货需传值1'
/

comment on column WHS_OTHER_DTL.ACTUAL_OPERATE_QTY is '贸易货实际装箱数量(基本单位)'
/

comment on column WHS_OTHER_DTL.CONTAINER_BRAND is '外箱品牌'
/

comment on column WHS_OTHER_DTL.COLOR_NUMBER is '色号（砖类）'
/

comment on column WHS_OTHER_DTL.THICKNESS is '砖厚'
/

comment on column WHS_OTHER_DTL.ACTUAL_AVG_GROSS_WEIGHT is '产品实际平均毛重'
/

comment on column WHS_OTHER_DTL.ACTUAL_PACKING_NUMBER is '实际装箱件数（销售单位）'
/

comment on column WHS_OTHER_DTL.CUSTOMS_DECLARATION_NUMBER is '报关件数'
/

comment on column WHS_OTHER_DTL.PACKING_SPEC is '包装规格'
/

comment on column WHS_OTHER_DTL.LOADING_TYPE is '装柜方式'
/

comment on column WHS_OTHER_DTL.STORAGE_OUT is '串货调整调出储位'
/

comment on column WHS_OTHER_DTL.STORAGE_IN is '串货调整调入储位'
/

create trigger TR_WHS_OTHER_DTL_BEF
	before insert
	on WHS_OTHER_DTL
	for each row
DECLARE
    V_NUM1 NUMBER := 0;
BEGIN
    SELECT NVL(TO_NUMBER(MAX(WOD.AUTO_ID)), 0) INTO V_NUM1 FROM WHS_OTHER_DTL WOD;
    :NEW.AUTO_ID := V_NUM1 + 1;
END;
/

create table WHS_REPACKAGE_DTL
(
	MATERIAL_NO VARCHAR2(18),
	BOX_NOTE VARCHAR2(50),
	REPACKAGE_BILL_NO VARCHAR2(16),
	DOMESTIC_QTY_RECEIVED NUMBER(13),
	STATUS VARCHAR2(2),
	UUID VARCHAR2(32) not null
		primary key,
	OPERATER_ID VARCHAR2(60),
	CREATE_TIME DATE,
	"LEVEL" NUMBER(13)
)
/

comment on column WHS_REPACKAGE_DTL.MATERIAL_NO is '物料号'
/

comment on column WHS_REPACKAGE_DTL.BOX_NOTE is '箱码'
/

comment on column WHS_REPACKAGE_DTL.REPACKAGE_BILL_NO is '拆箱单号'
/

comment on column WHS_REPACKAGE_DTL.DOMESTIC_QTY_RECEIVED is '实际数量'
/

comment on column WHS_REPACKAGE_DTL.STATUS is '是否SAP同步成功（0=不成功，1=成功）'
/

comment on column WHS_REPACKAGE_DTL.UUID is 'UUID'
/

comment on column WHS_REPACKAGE_DTL.OPERATER_ID is '员工工号'
/

comment on column WHS_REPACKAGE_DTL.CREATE_TIME is '时间'
/

comment on column WHS_REPACKAGE_DTL."LEVEL" is '层级'
/

create table WHS_DUMP
(
	WHS_DUMP_UUID VARCHAR2(32) not null,
	WHS_DUMP_NO VARCHAR2(32),
	GOODS_TYPE VARCHAR2(2),
	BUSINESS_TYPE VARCHAR2(16),
	WERKS_DUMP_OUT VARCHAR2(6),
	WHS_LOCATION_CODE_DUMP_OUT VARCHAR2(6),
	DUMP_OUT_REMARK VARCHAR2(255),
	CREATE_USERID VARCHAR2(10),
	CREATE_DATE VARCHAR2(16),
	DUMP_OUT_OPERATOR VARCHAR2(10),
	DUMP_OUT_DATE VARCHAR2(16),
	SPMS_STATUS VARCHAR2(2),
	DUMP_OUT_SAP_DATE VARCHAR2(16),
	SAP_DUMP_OUT_NO VARCHAR2(32),
	SAP_DELIVERY_NOTE VARCHAR2(16),
	SAP_WHS_OUT_NO VARCHAR2(32),
	WERKS_DUMP_IN VARCHAR2(6),
	WHS_LOCATION_CODE_DUMP_IN VARCHAR2(6),
	DUMP_IN_REMARK VARCHAR2(255),
	DUMP_IN_OPERATOR VARCHAR2(10),
	DUMP_IN_DATE VARCHAR2(16),
	SAP_WHS_IN_NO VARCHAR2(32),
	SUPPLIER_CODE VARCHAR2(16),
	VOUCHER_TYPE VARCHAR2(16),
	PURCHASE_GROUP VARCHAR2(16),
	PURCHASE_ORG VARCHAR2(16),
	RETURN_MARK VARCHAR2(16),
	REMARK VARCHAR2(255),
	DUMP_IN_SAP_DATE VARCHAR2(16),
	DELIVERY_DATE VARCHAR2(16),
	CANCEL_VOUCHER_NUMBER VARCHAR2(16),
	CANCEL_POSTING_DATE VARCHAR2(10),
	CANCEL_VOUCHER_YEAR VARCHAR2(16),
	SAP_WHS_OUT_YEAR VARCHAR2(16)
)
/

comment on table WHS_DUMP is '转储任务表'
/

comment on column WHS_DUMP.WHS_DUMP_UUID is '转储任务UUID'
/

comment on column WHS_DUMP.WHS_DUMP_NO is '转储任务单号'
/

comment on column WHS_DUMP.GOODS_TYPE is '货物类型'
/

comment on column WHS_DUMP.BUSINESS_TYPE is '业务标识'
/

comment on column WHS_DUMP.WERKS_DUMP_OUT is '转出工厂代码-CN01'
/

comment on column WHS_DUMP.WHS_LOCATION_CODE_DUMP_OUT is '转出仓库代码'
/

comment on column WHS_DUMP.DUMP_OUT_REMARK is '转出备注信息'
/

comment on column WHS_DUMP.CREATE_USERID is '创建人工号'
/

comment on column WHS_DUMP.CREATE_DATE is '创建日期'
/

comment on column WHS_DUMP.DUMP_OUT_OPERATOR is '转出执行人'
/

comment on column WHS_DUMP.DUMP_OUT_DATE is '转出完成日期'
/

comment on column WHS_DUMP.SPMS_STATUS is 'SPMS转储单据状态（0删除；1草稿；2转出可执行；3转出完成；4提交SAP获取PO成功；5提交SAP获取PO失败；6提交SAP获取DN成功；7提交SAP获取DN失败；8转出已撤销；）'
/

comment on column WHS_DUMP.DUMP_OUT_SAP_DATE is '转出提交SAP日期/过账日期'
/

comment on column WHS_DUMP.SAP_DUMP_OUT_NO is 'SAP转储单号/采购凭证号'
/

comment on column WHS_DUMP.SAP_DELIVERY_NOTE is 'SAP交货单号'
/

comment on column WHS_DUMP.SAP_WHS_OUT_NO is 'SAP转出物料凭证号/获取DN时'
/

comment on column WHS_DUMP.WERKS_DUMP_IN is '转入工厂代码/客户编号'
/

comment on column WHS_DUMP.WHS_LOCATION_CODE_DUMP_IN is '转入仓库代码'
/

comment on column WHS_DUMP.DUMP_IN_REMARK is '转入备注信息'
/

comment on column WHS_DUMP.DUMP_IN_OPERATOR is '转入执行人'
/

comment on column WHS_DUMP.DUMP_IN_DATE is '转入完成日期'
/

comment on column WHS_DUMP.SAP_WHS_IN_NO is 'SAP转入物料凭证号'
/

comment on column WHS_DUMP.SUPPLIER_CODE is '供应商编码'
/

comment on column WHS_DUMP.VOUCHER_TYPE is '凭证类型/订单类型(采购)ZC09'
/

comment on column WHS_DUMP.PURCHASE_GROUP is '采购组-260'
/

comment on column WHS_DUMP.PURCHASE_ORG is '采购组织-1000'
/

comment on column WHS_DUMP.RETURN_MARK is '退货标识'
/

comment on column WHS_DUMP.REMARK is '备注信息'
/

comment on column WHS_DUMP.DUMP_IN_SAP_DATE is '转入提交SAP日期'
/

comment on column WHS_DUMP.DELIVERY_DATE is '项目交货日期'
/

comment on column WHS_DUMP.CANCEL_VOUCHER_NUMBER is '撤销SAP凭证号'
/

comment on column WHS_DUMP.CANCEL_POSTING_DATE is '撤销SAP过账日期'
/

comment on column WHS_DUMP.CANCEL_VOUCHER_YEAR is '撤销SAP凭证年份'
/

comment on column WHS_DUMP.SAP_WHS_OUT_YEAR is 'SAP转出物料凭证年份/获取DN时'
/

create unique index WHS_DUMP_WHS_DUMP_UUID_UINDEX
	on WHS_DUMP (WHS_DUMP_UUID)
/

alter table WHS_DUMP
	add constraint WHS_DUMP_PK
		primary key (WHS_DUMP_UUID)
/

create trigger TR_WHS_DUMP_BEF
	before insert
	on WHS_DUMP
	for each row
DECLARE
    V_NUM1 NUMBER := 0;
BEGIN
    SELECT NVL(TO_NUMBER(SUBSTR(MAX(WD.WHS_DUMP_NO), -3, 3)), 0) INTO V_NUM1 FROM WHS_DUMP WD
    WHERE WD.CREATE_DATE = :NEW.CREATE_DATE
      AND WD.WERKS_DUMP_OUT = :NEW.WERKS_DUMP_OUT
      AND WD.WHS_LOCATION_CODE_DUMP_OUT = :NEW.WHS_LOCATION_CODE_DUMP_OUT;
    :NEW.WHS_DUMP_NO := :NEW.WERKS_DUMP_OUT||'-'||:NEW.WHS_LOCATION_CODE_DUMP_OUT||'-'||REPLACE(:NEW.CREATE_DATE, '-')||'-'|| SUBSTR(1001 + V_NUM1, 2, 4);
END;
/

create table WHS_DUMP_DTL
(
	WHS_DUMP_UUID VARCHAR2(32),
	ITEM NUMBER,
	MATERIAL_NO VARCHAR2(40),
	BASIC_UNIT VARCHAR2(6),
	BOX_NOTE VARCHAR2(32),
	PLANNED_OUT_QTY NUMBER,
	PLANNED_OUT_STORAGE VARCHAR2(4000),
	ACTUAL_OUT_QTY NUMBER,
	ACTUAL_OUT_STORAGE VARCHAR2(4000),
	DUMP_OUT_REMARK VARCHAR2(255),
	ACTUAL_IN_QTY NUMBER,
	ACTUAL_IN_STORAGE VARCHAR2(4000),
	DUMP_IN_REMARK VARCHAR2(255),
	SAP_DELIVERY_NOTE VARCHAR2(32),
	SAP_DELIVERY_NOTE_ITEM VARCHAR2(6),
	SAP_WHS_OUT_NO VARCHAR2(32),
	SAP_WHS_OUT_NO_ITEM VARCHAR2(6),
	SAP_WHS_IN_NO VARCHAR2(32),
	SAP_WHS_IN_NO_ITEM VARCHAR2(6),
	SAP_DUMP_OUT_NO VARCHAR2(32),
	SAP_DUMP_OUT_NO_ITEM VARCHAR2(6),
	OVER_DELIVERY_LIMIT NUMBER
)
/

comment on table WHS_DUMP_DTL is '转储任务明细表'
/

comment on column WHS_DUMP_DTL.WHS_DUMP_UUID is '转储任务UUID'
/

comment on column WHS_DUMP_DTL.ITEM is '任务行号'
/

comment on column WHS_DUMP_DTL.MATERIAL_NO is '物料编号'
/

comment on column WHS_DUMP_DTL.BASIC_UNIT is '基本单位'
/

comment on column WHS_DUMP_DTL.BOX_NOTE is '箱码'
/

comment on column WHS_DUMP_DTL.PLANNED_OUT_QTY is '计划转出数量（基本单位）'
/

comment on column WHS_DUMP_DTL.PLANNED_OUT_STORAGE is '计划转出储位信息'
/

comment on column WHS_DUMP_DTL.ACTUAL_OUT_QTY is '实际转出数量（基本单位）'
/

comment on column WHS_DUMP_DTL.ACTUAL_OUT_STORAGE is '实际转出储位信息'
/

comment on column WHS_DUMP_DTL.DUMP_OUT_REMARK is '转出备注信息'
/

comment on column WHS_DUMP_DTL.ACTUAL_IN_QTY is '实际转入数量（基本单位）'
/

comment on column WHS_DUMP_DTL.ACTUAL_IN_STORAGE is '实际转入储位信息'
/

comment on column WHS_DUMP_DTL.DUMP_IN_REMARK is '转入备注信息'
/

comment on column WHS_DUMP_DTL.SAP_DELIVERY_NOTE is 'SAP交货单号'
/

comment on column WHS_DUMP_DTL.SAP_DELIVERY_NOTE_ITEM is 'SAP交货单行号'
/

comment on column WHS_DUMP_DTL.SAP_WHS_OUT_NO is 'SAP转出物料凭证号'
/

comment on column WHS_DUMP_DTL.SAP_WHS_OUT_NO_ITEM is 'SAP转出物料凭证行号'
/

comment on column WHS_DUMP_DTL.SAP_WHS_IN_NO is 'SAP转入物料凭证号'
/

comment on column WHS_DUMP_DTL.SAP_WHS_IN_NO_ITEM is 'SAP转入物料凭证行号'
/

comment on column WHS_DUMP_DTL.SAP_DUMP_OUT_NO is 'SAP转储订单号/采购凭证号'
/

comment on column WHS_DUMP_DTL.SAP_DUMP_OUT_NO_ITEM is 'SAP转储订单行号'
/

comment on column WHS_DUMP_DTL.OVER_DELIVERY_LIMIT is '过量交货限度默认10（表示10%）'
/

create table WHS_REPACKAGE_NOTE
(
	REPACKAGE_BILL_NO VARCHAR2(32) not null
		primary key,
	OPERATE_TIME DATE,
	WORK_ID VARCHAR2(60),
	STATUS VARCHAR2(10)
)
/

create table Z_TEST
(
	UUID VARCHAR2(32) not null
		constraint Z_TEST_PK
			primary key,
	NUMBER_A NUMBER,
	NUMBER_B NUMBER(5),
	NUMBER_C NUMBER(5,2),
	NUMBER_D NUMBER(17,6),
	SPMS_STATUS VARCHAR2(2),
	CREATE_TIME DATE default TO_DATE(TO_CHAR(SYS_EXTRACT_UTC(SYSTIMESTAMP), 'yyyy-mm-dd hh24:mi:ss'), 'yyyy-mm-dd hh24:mi:ss'),
	AUTO_ID NUMBER default "SPMS"."Z_TEST_SEQ"."NEXTVAL",
	UPDATE_TIME DATE default TO_DATE(TO_CHAR(SYS_EXTRACT_UTC(SYSTIMESTAMP), 'yyyy-mm-dd hh24:mi:ss'), 'yyyy-mm-dd hh24:mi:ss'),
	SPMS_ITEM NUMBER,
	VERSION_NUMBER NUMBER default 1,
	VERSION_NUMBER1 NUMBER default null
)
/

comment on table Z_TEST is '忽略测试表'
/

comment on column Z_TEST.UUID is 'UUID主键'
/

comment on column Z_TEST.NUMBER_A is '数字A'
/

comment on column Z_TEST.NUMBER_B is '数字B'
/

comment on column Z_TEST.NUMBER_C is '数字C'
/

comment on column Z_TEST.NUMBER_D is '数字D'
/

comment on column Z_TEST.SPMS_STATUS is '状态测试'
/

comment on column Z_TEST.CREATE_TIME is '创建时间'
/

comment on column Z_TEST.AUTO_ID is 'sequence自增ID'
/

comment on column Z_TEST.UPDATE_TIME is '更新时间'
/

comment on column Z_TEST.SPMS_ITEM is 'SPMS行号'
/

comment on column Z_TEST.VERSION_NUMBER is '本条数据版本号更新自增加1方式'
/

comment on column Z_TEST.VERSION_NUMBER1 is 'sequence方式版本号不连续'
/

create trigger TR_Z_TEST_BEF
	before update
	on Z_TEST
	for each row
DECLARE
      V_NUM1 NUMBER := 0;
    BEGIN
      :NEW.UPDATE_TIME := TO_DATE(TO_CHAR(SYS_EXTRACT_UTC(SYSTIMESTAMP), 'yyyy-mm-dd hh24:mi:ss'), 'yyyy-mm-dd hh24:mi:ss');
      :NEW.VERSION_NUMBER := Z_TEST_SEQ.nextval + 1;
    END;
/

create trigger TR_Z_TEST_BEF_INSERT
	before insert
	on Z_TEST
	for each row
DECLARE
    V_NUM1 NUMBER := 0;
BEGIN
    IF (NVL(:NEW.SPMS_ITEM, 0) = 0)
        THEN
            SELECT NVL(MAX(Z.SPMS_ITEM), 0) INTO V_NUM1 FROM Z_TEST Z WHERE Z.NUMBER_A = :NEW.NUMBER_A;
            :NEW.SPMS_ITEM := V_NUM1 + 10;
    END IF;
END;
/

create table WHS_INVENTORY_AGE
(
	MATERIAL_NO VARCHAR2(18) not null,
	WERKS VARCHAR2(255) not null,
	WHS_LOCATION_CODE VARCHAR2(255) not null,
	AGE NUMBER,
	CHECK_DATE DATE,
	constraint SYS_C0043942
		primary key (MATERIAL_NO, WERKS, WHS_LOCATION_CODE)
)
/

create table OVERSEA_WHS_IN
(
	UUID_WHS_IN VARCHAR2(32) not null
		constraint OVERSEA_WHS_IN_PK
			primary key,
	WHS_IN_NO VARCHAR2(32),
	SAP_PACKAGE_LIST VARCHAR2(16),
	SAP_DELIVERY_NOTE VARCHAR2(16),
	WERKS VARCHAR2(10),
	WHS_LOCATION_CODE VARCHAR2(10),
	SPMS_STATUS VARCHAR2(2) default '0',
	CREATE_DATE VARCHAR2(12),
	CREATE_USERID VARCHAR2(32),
	REMARKS VARCHAR2(255),
	COMMENTS VARCHAR2(255),
	POSTING_DATE VARCHAR2(16),
	SAP_VOUCHER_NUMBER VARCHAR2(32),
	SAP_VOUCHER_YEAR VARCHAR2(16),
	CANCEL_POSTING_DATE VARCHAR2(16),
	CANCEL_VOUCHER_NUMBER VARCHAR2(32),
	CANCEL_VOUCHER_YEAR VARCHAR2(16),
	DATA_VERSION NUMBER default "SPMS"."DATA_VERSION"."NEXTVAL",
	CREATE_TIME DATE default TO_DATE(TO_CHAR(SYS_EXTRACT_UTC(SYSTIMESTAMP), 'yyyy-mm-dd hh24:mi:ss'), 'yyyy-mm-dd hh24:mi:ss'),
	UPDATE_TIME DATE default TO_DATE(TO_CHAR(SYS_EXTRACT_UTC(SYSTIMESTAMP), 'yyyy-mm-dd hh24:mi:ss'), 'yyyy-mm-dd hh24:mi:ss'),
	CABINET_NUMBER VARCHAR2(50)
)
/

comment on table OVERSEA_WHS_IN is '海外仓入库任务表头'
/

comment on column OVERSEA_WHS_IN.UUID_WHS_IN is '入库任务UUID'
/

comment on column OVERSEA_WHS_IN.WHS_IN_NO is '入库任务单号'
/

comment on column OVERSEA_WHS_IN.SAP_PACKAGE_LIST is '装箱单号'
/

comment on column OVERSEA_WHS_IN.SAP_DELIVERY_NOTE is '交货单号'
/

comment on column OVERSEA_WHS_IN.WERKS is '入库任务工厂'
/

comment on column OVERSEA_WHS_IN.WHS_LOCATION_CODE is '入库任务仓库库存地点'
/

comment on column OVERSEA_WHS_IN.SPMS_STATUS is '入库任务状态(默认0待入库;1入库中;2入库完成;3审批通过;4提交SAP成功;5提交SAP失败;6已撤销;7关闭;)'
/

comment on column OVERSEA_WHS_IN.CREATE_DATE is '创建日期'
/

comment on column OVERSEA_WHS_IN.CREATE_USERID is '创建人工号'
/

comment on column OVERSEA_WHS_IN.REMARKS is '入库任务备注'
/

comment on column OVERSEA_WHS_IN.COMMENTS is '入库任务评论'
/

comment on column OVERSEA_WHS_IN.POSTING_DATE is '提交SAP过账日期'
/

comment on column OVERSEA_WHS_IN.SAP_VOUCHER_NUMBER is 'SAP物料凭证号'
/

comment on column OVERSEA_WHS_IN.SAP_VOUCHER_YEAR is 'SAP物料凭证年份'
/

comment on column OVERSEA_WHS_IN.CANCEL_POSTING_DATE is '撤销提交SAP过账日期'
/

comment on column OVERSEA_WHS_IN.CANCEL_VOUCHER_NUMBER is '撤销提交SAP物料凭证号'
/

comment on column OVERSEA_WHS_IN.CANCEL_VOUCHER_YEAR is '撤销提交SAP物料凭证年份'
/

comment on column OVERSEA_WHS_IN.DATA_VERSION is '数据版本号'
/

comment on column OVERSEA_WHS_IN.CREATE_TIME is '创建时间'
/

comment on column OVERSEA_WHS_IN.UPDATE_TIME is '更新时间'
/

comment on column OVERSEA_WHS_IN.CABINET_NUMBER is '柜号'
/

create trigger TR_OVERSEA_WHS_IN_BEF_UPDATE
	before update
	on OVERSEA_WHS_IN
	for each row
BEGIN
    :new.UPDATE_TIME := TO_DATE(TO_CHAR(SYS_EXTRACT_UTC(SYSTIMESTAMP), 'yyyy-mm-dd hh24:mi:ss'), 'yyyy-mm-dd hh24:mi:ss');
    :new.DATA_VERSION := DATA_VERSION.nextval;
END;
/

create trigger TR_OVERSEA_WHS_IN_BEF_INSERT
	before insert
	on OVERSEA_WHS_IN
	for each row
DECLARE
    V_NUM1 NUMBER := 0;
BEGIN
    SELECT NVL(TO_NUMBER(SUBSTR(MAX(WI.WHS_IN_NO), 15, 17)), 0) INTO V_NUM1 FROM OVERSEA_WHS_IN WI
    WHERE WI.CREATE_DATE = :NEW.CREATE_DATE AND WI.WERKS = :NEW.WERKS;
    :NEW.WHS_IN_NO := :NEW.WERKS||'-'||REPLACE(:NEW.CREATE_DATE, '-', '')|| '-'|| SUBSTR(1001 + V_NUM1, 2, 4);
END;
/

create table OVERSEA_WHS_IN_DTL
(
	AUTO_ID_WHS_IN_DTL NUMBER default "SPMS"."ITEM_AUTO_ID_SEQ"."NEXTVAL" not null
		constraint OVERSEA_WHS_IN_DTL_PK
			primary key,
	UUID_WHS_IN VARCHAR2(32),
	ITEM VARCHAR2(6),
	MATERIAL_NO VARCHAR2(32),
	BASIC_UNIT VARCHAR2(6),
	UNIT_SALES VARCHAR2(6),
	RECEIVABLE_QTY_BASIC_UNIT NUMBER,
	RECEIVABLE_QTY_UNIT_SALES NUMBER,
	ACTUAL_QTY_BASIC_UNIT NUMBER,
	ACTUAL_QTY_UNIT_SALES NUMBER,
	STORAGE_INFO VARCHAR2(2000),
	ITEM_REMARKS VARCHAR2(255),
	SAP_DELIVERY_NOTE VARCHAR2(16),
	SAP_DELIVERY_NOTE_ITEM VARCHAR2(6),
	RECEIVABLE_WERKS VARCHAR2(16),
	RECEIVABLE_WHS_LOCATION_CODE VARCHAR2(16),
	ACTUAL_WERKS VARCHAR2(16),
	ACTUAL_WHS_LOCATION_CODE VARCHAR2(16),
	BOX_NOTE VARCHAR2(50),
	BOX_NOTE_ITEM VARCHAR2(6)
)
/

comment on table OVERSEA_WHS_IN_DTL is '海外仓入库任务明细'
/

comment on column OVERSEA_WHS_IN_DTL.AUTO_ID_WHS_IN_DTL is '入库任务明细自增ID'
/

comment on column OVERSEA_WHS_IN_DTL.UUID_WHS_IN is '入库任务UUID'
/

comment on column OVERSEA_WHS_IN_DTL.ITEM is '入库任务行号'
/

comment on column OVERSEA_WHS_IN_DTL.MATERIAL_NO is '物料编号'
/

comment on column OVERSEA_WHS_IN_DTL.BASIC_UNIT is '基本单位'
/

comment on column OVERSEA_WHS_IN_DTL.UNIT_SALES is '销售单位'
/

comment on column OVERSEA_WHS_IN_DTL.RECEIVABLE_QTY_BASIC_UNIT is '应收基本单位数量'
/

comment on column OVERSEA_WHS_IN_DTL.RECEIVABLE_QTY_UNIT_SALES is '应收销售单位数量'
/

comment on column OVERSEA_WHS_IN_DTL.ACTUAL_QTY_BASIC_UNIT is '实收基本单位数量'
/

comment on column OVERSEA_WHS_IN_DTL.ACTUAL_QTY_UNIT_SALES is '实收销售单位数量'
/

comment on column OVERSEA_WHS_IN_DTL.STORAGE_INFO is '储位信息'
/

comment on column OVERSEA_WHS_IN_DTL.ITEM_REMARKS is 'SPMS行项目备注'
/

comment on column OVERSEA_WHS_IN_DTL.SAP_DELIVERY_NOTE is '交货单号'
/

comment on column OVERSEA_WHS_IN_DTL.SAP_DELIVERY_NOTE_ITEM is '交货单行号'
/

comment on column OVERSEA_WHS_IN_DTL.RECEIVABLE_WERKS is '收货工厂——应收'
/

comment on column OVERSEA_WHS_IN_DTL.RECEIVABLE_WHS_LOCATION_CODE is '收货库存地点——应收'
/

comment on column OVERSEA_WHS_IN_DTL.ACTUAL_WERKS is '收货工厂——实收'
/

comment on column OVERSEA_WHS_IN_DTL.ACTUAL_WHS_LOCATION_CODE is '收货库存地点——实收'
/

comment on column OVERSEA_WHS_IN_DTL.BOX_NOTE is '箱码'
/

comment on column OVERSEA_WHS_IN_DTL.BOX_NOTE_ITEM is '箱码明细行号'
/

create table OVERSEA_OPERATE_LOG
(
	AUTO_ID_LOG NUMBER default "SPMS"."OVERSEA_OPERATE_LOG_SEQ"."NEXTVAL" not null
		constraint OVERSEA_OPERATE_LOG_PK
			primary key,
	DOCUMENT_NUMBER VARCHAR2(32),
	LOG_CONTENT VARCHAR2(255),
	LOG_CONTENT_EN VARCHAR2(255),
	LOG_TYPE VARCHAR2(2) default '1',
	REQUEST_PARAM CLOB,
	RETURNED_MESSAGE CLOB,
	REMARK VARCHAR2(1000),
	USER_ID VARCHAR2(16),
	CREATE_TIME DATE default TO_DATE(TO_CHAR(SYS_EXTRACT_UTC(SYSTIMESTAMP), 'yyyy-mm-dd hh24:mi:ss'), 'yyyy-mm-dd hh24:mi:ss')
)
/

comment on table OVERSEA_OPERATE_LOG is '海外仓操作日志表'
/

comment on column OVERSEA_OPERATE_LOG.AUTO_ID_LOG is '自增ID'
/

comment on column OVERSEA_OPERATE_LOG.DOCUMENT_NUMBER is '关联单据号(自增ID或UUID)'
/

comment on column OVERSEA_OPERATE_LOG.LOG_CONTENT is '日志内容描述'
/

comment on column OVERSEA_OPERATE_LOG.LOG_CONTENT_EN is '日志内容英文描述'
/

comment on column OVERSEA_OPERATE_LOG.LOG_TYPE is '日志类型(默认1单据SPMS操作日志;2请求SAP日志;3请求OA日志;)'
/

comment on column OVERSEA_OPERATE_LOG.REQUEST_PARAM is '请求参数'
/

comment on column OVERSEA_OPERATE_LOG.RETURNED_MESSAGE is '返回参数'
/

comment on column OVERSEA_OPERATE_LOG.REMARK is '备注信息'
/

comment on column OVERSEA_OPERATE_LOG.USER_ID is '用户工号'
/

comment on column OVERSEA_OPERATE_LOG.CREATE_TIME is '创建时间'
/

create table OVERSEA_REQUISITION
(
	AUTO_ID_REQUISITION NUMBER default "SPMS"."OVERSEA_REQUISITION_SEQ"."NEXTVAL",
	UUID_REQUISITION VARCHAR2(32) not null
		constraint OVERSEA_REQUISITION_PK
			primary key,
	SPMS_ID VARCHAR2(32),
	CREATE_DATE VARCHAR2(16),
	BUSINESS_TYPE VARCHAR2(32),
	SPMS_STATUS VARCHAR2(2) default '1',
	CREATE_USERID VARCHAR2(16),
	WERKS VARCHAR2(16),
	WORKSHOP_CODE VARCHAR2(32),
	WHS_LOCATION_CODE VARCHAR2(16),
	RELATE_DOCUMENT VARCHAR2(32),
	COST_CENTER VARCHAR2(32),
	DATA_VERSION NUMBER default "SPMS"."DATA_VERSION"."NEXTVAL",
	REMARKS VARCHAR2(255),
	COMMENTS VARCHAR2(255),
	CREATE_TIME DATE default TO_DATE(TO_CHAR(SYS_EXTRACT_UTC(SYSTIMESTAMP), 'yyyy-mm-dd hh24:mi:ss'), 'yyyy-mm-dd hh24:mi:ss'),
	UPDATE_TIME DATE default TO_DATE(TO_CHAR(SYS_EXTRACT_UTC(SYSTIMESTAMP), 'yyyy-mm-dd hh24:mi:ss'), 'yyyy-mm-dd hh24:mi:ss'),
	COST_CENTER_NAME VARCHAR2(255)
)
/

comment on table OVERSEA_REQUISITION is '领用申请表'
/

comment on column OVERSEA_REQUISITION.AUTO_ID_REQUISITION is '领用申请表自增ID'
/

comment on column OVERSEA_REQUISITION.UUID_REQUISITION is '领用申请UUID'
/

comment on column OVERSEA_REQUISITION.SPMS_ID is '领用申请单号'
/

comment on column OVERSEA_REQUISITION.CREATE_DATE is '创建日期'
/

comment on column OVERSEA_REQUISITION.BUSINESS_TYPE is '业务类型'
/

comment on column OVERSEA_REQUISITION.SPMS_STATUS is '单据状态(0已删除;1待提交;2待审批;3审批通过;4审批拒绝;5已生成出库任务;)'
/

comment on column OVERSEA_REQUISITION.CREATE_USERID is '申请人'
/

comment on column OVERSEA_REQUISITION.WERKS is '工厂代码'
/

comment on column OVERSEA_REQUISITION.WORKSHOP_CODE is '车间代码'
/

comment on column OVERSEA_REQUISITION.WHS_LOCATION_CODE is '仓库地点代码'
/

comment on column OVERSEA_REQUISITION.RELATE_DOCUMENT is '来源单号'
/

comment on column OVERSEA_REQUISITION.COST_CENTER is '成本中心'
/

comment on column OVERSEA_REQUISITION.DATA_VERSION is '数据版本号'
/

comment on column OVERSEA_REQUISITION.REMARKS is '备注/申请原因'
/

comment on column OVERSEA_REQUISITION.COMMENTS is '评论/标题'
/

comment on column OVERSEA_REQUISITION.CREATE_TIME is '创建时间'
/

comment on column OVERSEA_REQUISITION.UPDATE_TIME is '更新时间'
/

comment on column OVERSEA_REQUISITION.COST_CENTER_NAME is '成本中心名称'
/

create trigger TR_OVERSEA_REQUISITION_BEF_UPDATE
	before update
	on OVERSEA_REQUISITION
	for each row
BEGIN
    :new.UPDATE_TIME := TO_DATE(TO_CHAR(SYS_EXTRACT_UTC(SYSTIMESTAMP), 'yyyy-mm-dd hh24:mi:ss'), 'yyyy-mm-dd hh24:mi:ss');
    :new.DATA_VERSION := DATA_VERSION.nextval;
END;
/

create trigger TR_OVERSEA_REQUISITION_INSERT
	before insert
	on OVERSEA_REQUISITION
	for each row
DECLARE
    V_NUM1 NUMBER := 0;
BEGIN
    SELECT NVL(TO_NUMBER(SUBSTR(MAX(ORQ.SPMS_ID), -3, 3)), 0) INTO V_NUM1 FROM OVERSEA_REQUISITION ORQ
    WHERE ORQ.CREATE_DATE = :NEW.CREATE_DATE
      AND ORQ.WERKS = :NEW.WERKS
      AND ORQ.WORKSHOP_CODE = :NEW.WORKSHOP_CODE;
    :NEW.SPMS_ID := :NEW.WERKS||'-'||:NEW.WORKSHOP_CODE||'-'||REPLACE(:NEW.CREATE_DATE, '-')||'-'||SUBSTR(1001 + V_NUM1, 2, 4);
END;
/

create table OVERSEA_WHS_OUT
(
	AUTO_ID_WHS_OUT NUMBER default "SPMS"."OVERSEA_WHS_OUT_SEQ"."NEXTVAL",
	UUID_WHS_OUT VARCHAR2(32) not null
		constraint OVERSEA_WHS_OUT_PK
			primary key,
	SPMS_ID VARCHAR2(32),
	CREATE_DATE VARCHAR2(16),
	MOVEMENT_TYPE VARCHAR2(32),
	SPMS_STATUS VARCHAR2(2) default '1',
	CREATE_USERID VARCHAR2(16),
	WERKS VARCHAR2(16),
	WORKSHOP_CODE VARCHAR2(16),
	WHS_LOCATION_CODE VARCHAR2(16),
	RELATE_DOCUMENT VARCHAR2(32),
	COST_CENTER VARCHAR2(32),
	DATA_VERSION NUMBER default "SPMS"."DATA_VERSION"."NEXTVAL",
	REMARKS VARCHAR2(255),
	COMMENTS VARCHAR2(255),
	POSTING_DATE VARCHAR2(16),
	SAP_VOUCHER_NUMBER VARCHAR2(32),
	SAP_VOUCHER_YEAR VARCHAR2(16),
	CANCEL_POSTING_DATE VARCHAR2(16),
	SAP_CANCEL_VOUCHER_NUMBER VARCHAR2(32),
	SAP_CANCEL_VOUCHER_YEAR VARCHAR2(16),
	CREATE_TIME DATE default TO_DATE(TO_CHAR(SYS_EXTRACT_UTC(SYSTIMESTAMP), 'yyyy-mm-dd hh24:mi:ss'), 'yyyy-mm-dd hh24:mi:ss'),
	UPDATE_TIME DATE default TO_DATE(TO_CHAR(SYS_EXTRACT_UTC(SYSTIMESTAMP), 'yyyy-mm-dd hh24:mi:ss'), 'yyyy-mm-dd hh24:mi:ss'),
	COST_CENTER_NAME VARCHAR2(255)
)
/

comment on table OVERSEA_WHS_OUT is '海外出库任务表'
/

comment on column OVERSEA_WHS_OUT.AUTO_ID_WHS_OUT is '出库任务表自增ID'
/

comment on column OVERSEA_WHS_OUT.UUID_WHS_OUT is '出库任务UUID'
/

comment on column OVERSEA_WHS_OUT.SPMS_ID is '出库任务单号'
/

comment on column OVERSEA_WHS_OUT.CREATE_DATE is '创建日期'
/

comment on column OVERSEA_WHS_OUT.MOVEMENT_TYPE is '移动类型'
/

comment on column OVERSEA_WHS_OUT.SPMS_STATUS is '单据状态(0已删除;1出库中;2出库完成;3提交SAP成功;4提交SAP失败;5已撤销;)'
/

comment on column OVERSEA_WHS_OUT.CREATE_USERID is '创建人'
/

comment on column OVERSEA_WHS_OUT.WERKS is '工厂代码'
/

comment on column OVERSEA_WHS_OUT.WORKSHOP_CODE is '车间代码'
/

comment on column OVERSEA_WHS_OUT.WHS_LOCATION_CODE is '仓库地点代码'
/

comment on column OVERSEA_WHS_OUT.RELATE_DOCUMENT is '来源单号/关联单据号'
/

comment on column OVERSEA_WHS_OUT.COST_CENTER is '成本中心'
/

comment on column OVERSEA_WHS_OUT.DATA_VERSION is '数据版本号'
/

comment on column OVERSEA_WHS_OUT.REMARKS is '备注'
/

comment on column OVERSEA_WHS_OUT.COMMENTS is '评论/出库申请标题'
/

comment on column OVERSEA_WHS_OUT.POSTING_DATE is 'SAP过账日期'
/

comment on column OVERSEA_WHS_OUT.SAP_VOUCHER_NUMBER is 'SAP物料凭证'
/

comment on column OVERSEA_WHS_OUT.SAP_VOUCHER_YEAR is 'SAP物料凭证年份'
/

comment on column OVERSEA_WHS_OUT.CANCEL_POSTING_DATE is 'SAP撤销过账日期'
/

comment on column OVERSEA_WHS_OUT.SAP_CANCEL_VOUCHER_NUMBER is 'SAP撤销凭证'
/

comment on column OVERSEA_WHS_OUT.SAP_CANCEL_VOUCHER_YEAR is 'SAP撤销凭证年份'
/

comment on column OVERSEA_WHS_OUT.CREATE_TIME is '创建时间'
/

comment on column OVERSEA_WHS_OUT.UPDATE_TIME is '更新时间'
/

comment on column OVERSEA_WHS_OUT.COST_CENTER_NAME is '成本中心名称'
/

create trigger TR_OVERSEA_WHS_OUT_BEF_UPDATE
	before update
	on OVERSEA_WHS_OUT
	for each row
BEGIN
    :new.UPDATE_TIME := TO_DATE(TO_CHAR(SYS_EXTRACT_UTC(SYSTIMESTAMP), 'yyyy-mm-dd hh24:mi:ss'), 'yyyy-mm-dd hh24:mi:ss');
    :new.DATA_VERSION := DATA_VERSION.nextval;
END;
/

create trigger TR_OVERSEA_WHS_OUT_INSERT
	before insert
	on OVERSEA_WHS_OUT
	for each row
DECLARE
    V_NUM1 NUMBER := 0;
BEGIN
    SELECT NVL(TO_NUMBER(SUBSTR(MAX(OWO.SPMS_ID), -3, 3)), 0) INTO V_NUM1 FROM OVERSEA_WHS_OUT OWO
    WHERE OWO.CREATE_DATE = :NEW.CREATE_DATE
      AND OWO.WERKS = :NEW.WERKS
      AND OWO.WHS_LOCATION_CODE = :NEW.WHS_LOCATION_CODE;
    :NEW.SPMS_ID := :NEW.WERKS||'-'||:NEW.WHS_LOCATION_CODE||'-'||REPLACE(:NEW.CREATE_DATE, '-')||'-'||SUBSTR(1001 + V_NUM1, 2, 4);
END;
/

create table OVERSEA_REQUISITION_DTL
(
	AUTO_ID_REQUISITION_DTL NUMBER default "SPMS"."OVERSEA_REQUISITION_DTL_SEQ"."NEXTVAL" not null
		constraint OVERSEA_REQUISITION_DTL_PK
			primary key,
	UUID_REQUISITION VARCHAR2(32),
	ITEM NUMBER,
	WERKS VARCHAR2(16),
	WORKSHOP_CODE VARCHAR2(16),
	WHS_LOCATION_CODE VARCHAR2(16),
	MATERIAL_NO VARCHAR2(32),
	APPLY_QTY_BASIC_UNIT NUMBER,
	ACTUAL_QTY_BASIC_UNIT NUMBER,
	BASIC_UNIT VARCHAR2(6),
	APPLY_QTY_UNIT_SALES NUMBER,
	ACTUAL_QTY_UNIT_SALES NUMBER,
	UNIT_SALES VARCHAR2(6),
	REMARKS VARCHAR2(255),
	STORAGE_INFO VARCHAR2(2000),
	MOVEMENT_TYPE VARCHAR2(12),
	ACTUAL_MOVEMENT_TYPE VARCHAR2(12)
)
/

comment on table OVERSEA_REQUISITION_DTL is '领用申请表明细'
/

comment on column OVERSEA_REQUISITION_DTL.AUTO_ID_REQUISITION_DTL is '领用申请明细表自增ID'
/

comment on column OVERSEA_REQUISITION_DTL.UUID_REQUISITION is '领用申请UUID'
/

comment on column OVERSEA_REQUISITION_DTL.ITEM is '明细行号'
/

comment on column OVERSEA_REQUISITION_DTL.WERKS is '工厂代码'
/

comment on column OVERSEA_REQUISITION_DTL.WORKSHOP_CODE is '车间代码'
/

comment on column OVERSEA_REQUISITION_DTL.WHS_LOCATION_CODE is '仓库地点代码'
/

comment on column OVERSEA_REQUISITION_DTL.MATERIAL_NO is '物料编号'
/

comment on column OVERSEA_REQUISITION_DTL.APPLY_QTY_BASIC_UNIT is '申请基本单位数量'
/

comment on column OVERSEA_REQUISITION_DTL.ACTUAL_QTY_BASIC_UNIT is '实际基本单位数量'
/

comment on column OVERSEA_REQUISITION_DTL.BASIC_UNIT is '基本单位'
/

comment on column OVERSEA_REQUISITION_DTL.APPLY_QTY_UNIT_SALES is '申请销售单位数量'
/

comment on column OVERSEA_REQUISITION_DTL.ACTUAL_QTY_UNIT_SALES is '实际销售单位数量'
/

comment on column OVERSEA_REQUISITION_DTL.UNIT_SALES is '销售单位'
/

comment on column OVERSEA_REQUISITION_DTL.REMARKS is '明细备注'
/

comment on column OVERSEA_REQUISITION_DTL.STORAGE_INFO is '储位信息'
/

comment on column OVERSEA_REQUISITION_DTL.MOVEMENT_TYPE is '申请物料移动类型'
/

comment on column OVERSEA_REQUISITION_DTL.ACTUAL_MOVEMENT_TYPE is '实际移动类型(生成出库任务时)'
/

create table OVERSEA_WHS_OUT_DTL
(
	AUTO_ID_WHS_OUT_DTL NUMBER default "SPMS"."OVERSEA_WHS_OUT_DTL_SEQ"."NEXTVAL" not null
		primary key,
	UUID_WHS_OUT VARCHAR2(32),
	ITEM NUMBER,
	WERKS VARCHAR2(16),
	WORKSHOP_CODE VARCHAR2(16),
	WHS_LOCATION_CODE VARCHAR2(16),
	MATERIAL_NO VARCHAR2(32),
	ISSUE_QTY_BASIC_UNIT NUMBER,
	ACTUAL_QTY_BASIC_UNIT NUMBER,
	BASIC_UNIT VARCHAR2(6),
	ISSUE_QTY_UNIT_SALES NUMBER,
	ACTUAL_QTY_UNIT_SALES NUMBER,
	UNIT_SALES VARCHAR2(6),
	REMARKS VARCHAR2(255),
	STORAGE_INFO VARCHAR2(2000),
	MOVEMENT_TYPE VARCHAR2(10),
	RELATE_DOCUMENT VARCHAR2(32),
	RELATE_DOCUMENT_ITEM VARCHAR2(6)
)
/

comment on table OVERSEA_WHS_OUT_DTL is '出库任务表明细'
/

comment on column OVERSEA_WHS_OUT_DTL.AUTO_ID_WHS_OUT_DTL is '出库任务表明细自增ID'
/

comment on column OVERSEA_WHS_OUT_DTL.UUID_WHS_OUT is '出库任务UUID'
/

comment on column OVERSEA_WHS_OUT_DTL.ITEM is '明细行号'
/

comment on column OVERSEA_WHS_OUT_DTL.WERKS is '工厂代码'
/

comment on column OVERSEA_WHS_OUT_DTL.WORKSHOP_CODE is '车间代码'
/

comment on column OVERSEA_WHS_OUT_DTL.WHS_LOCATION_CODE is '仓库地点代码'
/

comment on column OVERSEA_WHS_OUT_DTL.MATERIAL_NO is '物料编号'
/

comment on column OVERSEA_WHS_OUT_DTL.ISSUE_QTY_BASIC_UNIT is '应出基本单位数量'
/

comment on column OVERSEA_WHS_OUT_DTL.ACTUAL_QTY_BASIC_UNIT is '实际基本单位数量'
/

comment on column OVERSEA_WHS_OUT_DTL.BASIC_UNIT is '基本单位'
/

comment on column OVERSEA_WHS_OUT_DTL.ISSUE_QTY_UNIT_SALES is '应出销售单位数量'
/

comment on column OVERSEA_WHS_OUT_DTL.ACTUAL_QTY_UNIT_SALES is '实际销售单位数量'
/

comment on column OVERSEA_WHS_OUT_DTL.UNIT_SALES is '销售单位'
/

comment on column OVERSEA_WHS_OUT_DTL.REMARKS is '明细备注'
/

comment on column OVERSEA_WHS_OUT_DTL.STORAGE_INFO is '储位信息'
/

comment on column OVERSEA_WHS_OUT_DTL.MOVEMENT_TYPE is '物料移动类型'
/

comment on column OVERSEA_WHS_OUT_DTL.RELATE_DOCUMENT is '关联单号/领用申请单号'
/

comment on column OVERSEA_WHS_OUT_DTL.RELATE_DOCUMENT_ITEM is '关联单号/领用申请单号明细行号'
/

create table OVERSEA_DELIVERY_NOTE
(
	UUID_DELIVERY_NOTE VARCHAR2(32) not null
		constraint OVERSEA_DELIVERY_NOTE_PK
			primary key,
	SAP_DELIVERY_NOTE VARCHAR2(16),
	SAP_PACKAGE_LIST VARCHAR2(16),
	CABINET_NUMBER VARCHAR2(20),
	DELIVERY_TYPE VARCHAR2(8),
	SPMS_STATUS VARCHAR2(2) default '0',
	WERKS_RECEIVE VARCHAR2(8),
	WERKS_DELIVERY VARCHAR2(8),
	CREATE_DATE VARCHAR2(12),
	ARRIVAL_DATE VARCHAR2(16),
	REMARKS VARCHAR2(255),
	COMMENTS VARCHAR2(255),
	POSTING_DATE VARCHAR2(16),
	SAP_VOUCHER_NUMBER VARCHAR2(32),
	SAP_VOUCHER_YEAR VARCHAR2(8),
	CANCEL_POSTING_DATE VARCHAR2(16),
	CANCEL_VOUCHER_NUMBER VARCHAR2(32),
	CANCEL_VOUCHER_YEAR VARCHAR2(8),
	SAP_REMARK VARCHAR2(255),
	DATA_STATUS VARCHAR2(2) default '0',
	DATA_VERSION NUMBER default "SPMS"."DATA_VERSION"."NEXTVAL",
	CREATE_TIME DATE default TO_DATE(TO_CHAR(SYS_EXTRACT_UTC(SYSTIMESTAMP), 'yyyy-mm-dd hh24:mi:ss'), 'yyyy-mm-dd hh24:mi:ss'),
	UPDATE_TIME DATE default TO_DATE(TO_CHAR(SYS_EXTRACT_UTC(SYSTIMESTAMP), 'yyyy-mm-dd hh24:mi:ss'), 'yyyy-mm-dd hh24:mi:ss'),
	ACTUAL_WERKS_RECEIVE VARCHAR2(6),
	OA_WORKFLOW VARCHAR2(16),
	OA_BIANHAO VARCHAR2(32),
	OA_REMARK VARCHAR2(255),
	SUBMIT_OA_DATE VARCHAR2(12),
	OA_APPROVAL_DATE VARCHAR2(12),
	OA_FIELD VARCHAR2(16),
	UNDER_VOUCHER_NUMBER VARCHAR2(32),
	UNDER_VOUCHER_YEAR VARCHAR2(8),
	UNDER_POSTING_DATE VARCHAR2(16),
	CANCEL_UNDER_POSTING_DATE VARCHAR2(16),
	CANCEL_UNDER_VOUCHER_NUMBER VARCHAR2(32),
	CANCEL_UNDER_VOUCHER_YEAR VARCHAR2(8),
	ACTUAL_WHS_LOCATION_CODE_RECEIVE VARCHAR2(12),
	COST_CENTER VARCHAR2(32)
)
/

comment on table OVERSEA_DELIVERY_NOTE is '海外仓交货单表'
/

comment on column OVERSEA_DELIVERY_NOTE.UUID_DELIVERY_NOTE is '交货单号uuid'
/

comment on column OVERSEA_DELIVERY_NOTE.SAP_DELIVERY_NOTE is '交货单号'
/

comment on column OVERSEA_DELIVERY_NOTE.SAP_PACKAGE_LIST is '装箱单号'
/

comment on column OVERSEA_DELIVERY_NOTE.CABINET_NUMBER is '货柜号'
/

comment on column OVERSEA_DELIVERY_NOTE.DELIVERY_TYPE is '交货类型'
/

comment on column OVERSEA_DELIVERY_NOTE.SPMS_STATUS is 'SPMS收货单据状态(默认0待到货;1收货中/SAP凭证撤销;2入库完成/提交OA成功;3提交OA失败;4OA审批不通过;5OA审批通过/提交SAP失败;6收货多收提交SAP失败;7收货多收提交SAP成功;8收货少收提交SAP失败;9收货少收提交SAP成功;10提交SAP完成;11收货完成;12关闭/废单;)'
/

comment on column OVERSEA_DELIVERY_NOTE.WERKS_RECEIVE is '收货工厂'
/

comment on column OVERSEA_DELIVERY_NOTE.WERKS_DELIVERY is '发货工厂'
/

comment on column OVERSEA_DELIVERY_NOTE.CREATE_DATE is '创建日期'
/

comment on column OVERSEA_DELIVERY_NOTE.ARRIVAL_DATE is '到货日期(第一次生成入库任务日期)'
/

comment on column OVERSEA_DELIVERY_NOTE.REMARKS is 'SPMS备注信息'
/

comment on column OVERSEA_DELIVERY_NOTE.COMMENTS is 'SPMS评论/预留'
/

comment on column OVERSEA_DELIVERY_NOTE.POSTING_DATE is '过账日期/少收提交SAP入库凭证'
/

comment on column OVERSEA_DELIVERY_NOTE.SAP_VOUCHER_NUMBER is 'SAP凭证号/少收提交SAP入库凭证'
/

comment on column OVERSEA_DELIVERY_NOTE.SAP_VOUCHER_YEAR is 'SAP凭证年份/少收提交SAP入库凭证'
/

comment on column OVERSEA_DELIVERY_NOTE.CANCEL_POSTING_DATE is '撤销SAP过账日期/少收提交SAP入库凭证'
/

comment on column OVERSEA_DELIVERY_NOTE.CANCEL_VOUCHER_NUMBER is '撤销SAP凭证/少收提交SAP入库凭证'
/

comment on column OVERSEA_DELIVERY_NOTE.CANCEL_VOUCHER_YEAR is '撤销SAP凭证年份/少收提交SAP入库凭证'
/

comment on column OVERSEA_DELIVERY_NOTE.SAP_REMARK is 'SAP交货单抬头文本'
/

comment on column OVERSEA_DELIVERY_NOTE.DATA_STATUS is '数据状态(默认0能被SAP更新数据；1禁止更新数据；)'
/

comment on column OVERSEA_DELIVERY_NOTE.DATA_VERSION is '数据版本号'
/

comment on column OVERSEA_DELIVERY_NOTE.CREATE_TIME is '创建时间'
/

comment on column OVERSEA_DELIVERY_NOTE.UPDATE_TIME is '更新时间'
/

comment on column OVERSEA_DELIVERY_NOTE.ACTUAL_WERKS_RECEIVE is '实际收货工厂(交货单分批收不允许跨工厂收货)'
/

comment on column OVERSEA_DELIVERY_NOTE.OA_WORKFLOW is 'OA流程编号'
/

comment on column OVERSEA_DELIVERY_NOTE.OA_BIANHAO is 'OA单据编号'
/

comment on column OVERSEA_DELIVERY_NOTE.OA_REMARK is '提交OA审批备注'
/

comment on column OVERSEA_DELIVERY_NOTE.SUBMIT_OA_DATE is '提交OA日期'
/

comment on column OVERSEA_DELIVERY_NOTE.OA_APPROVAL_DATE is 'OA审批通过日期'
/

comment on column OVERSEA_DELIVERY_NOTE.OA_FIELD is 'OA预留字段/存储SPMS生成提交OA流水单据号'
/

comment on column OVERSEA_DELIVERY_NOTE.UNDER_VOUCHER_NUMBER is 'SAP凭证号/少收凭证'
/

comment on column OVERSEA_DELIVERY_NOTE.UNDER_VOUCHER_YEAR is 'SAP凭证年份/少收凭证'
/

comment on column OVERSEA_DELIVERY_NOTE.UNDER_POSTING_DATE is '过账日期/少收凭证'
/

comment on column OVERSEA_DELIVERY_NOTE.CANCEL_UNDER_POSTING_DATE is '撤销SAP过账日期/少收凭证'
/

comment on column OVERSEA_DELIVERY_NOTE.CANCEL_UNDER_VOUCHER_NUMBER is '撤销SAP凭证/少收凭证'
/

comment on column OVERSEA_DELIVERY_NOTE.CANCEL_UNDER_VOUCHER_YEAR is '撤销SAP凭证年份/少收凭证'
/

comment on column OVERSEA_DELIVERY_NOTE.ACTUAL_WHS_LOCATION_CODE_RECEIVE is '实际收货仓库(第一次拆出入库任务时写入)'
/

comment on column OVERSEA_DELIVERY_NOTE.COST_CENTER is '成本中心'
/

create trigger TR_OVERSEA_DELIVERY_NOTE_BEF_UPDATE
	before update
	on OVERSEA_DELIVERY_NOTE
	for each row
BEGIN
    :new.UPDATE_TIME := TO_DATE(TO_CHAR(SYS_EXTRACT_UTC(SYSTIMESTAMP), 'yyyy-mm-dd hh24:mi:ss'), 'yyyy-mm-dd hh24:mi:ss');
    :new.DATA_VERSION := DATA_VERSION.nextval;
END;
/

create table OVERSEA_DELIVERY_NOTE_DTL
(
	AUTO_ID_DELIVERY_NOTE NUMBER default "SPMS"."OVERSEA_DELIVERY_NOTE_DTL_SEQ"."NEXTVAL" not null
		constraint OVERSEA_DELIVERY_NOTE_DTL_PK
			primary key,
	SAP_DELIVERY_NOTE VARCHAR2(16),
	SAP_DELIVERY_NOTE_ITEM VARCHAR2(6),
	MATERIAL_NO VARCHAR2(32),
	BASIC_UNIT VARCHAR2(6),
	UNIT_SALES VARCHAR2(6),
	RECEIVABLE_QTY_BASIC_UNIT NUMBER default 0,
	RECEIVABLE_QTY_UNIT_SALES NUMBER default 0,
	ACTUAL_QTY_BASIC_UNIT NUMBER default 0,
	ACTUAL_QTY_UNIT_SALES NUMBER default 0,
	PURCHASE_NO VARCHAR2(16),
	PURCHASE_NO_ITEM VARCHAR2(6),
	REMARKS VARCHAR2(255),
	WERKS_DELIVERY VARCHAR2(6),
	WHS_LOCATION_CODE_DELIVERY VARCHAR2(8),
	WERKS_RECEIVE VARCHAR2(6),
	WHS_LOCATION_CODE_RECEIVE VARCHAR2(8),
	OVERCHARGED_QTY_BASIC_UNIT NUMBER default 0,
	OVERCHARGED_QTY_UNIT_SALES NUMBER default 0,
	UNDERCHARGED_QTY_BASIC_UNIT NUMBER default 0,
	UNDERCHARGED_QTY_UNIT_SALES NUMBER default 0,
	MOVEMENT_TYPE VARCHAR2(6),
	SAP_REMARK VARCHAR2(255),
	COLOR_NO VARCHAR2(64),
	DATA_STATUS VARCHAR2(2) default '0',
	DATA_VERSION NUMBER default "SPMS"."DATA_VERSION"."NEXTVAL",
	CREATE_TIME DATE default TO_DATE(TO_CHAR(SYS_EXTRACT_UTC(SYSTIMESTAMP), 'yyyy-mm-dd hh24:mi:ss'), 'yyyy-mm-dd hh24:mi:ss'),
	UPDATE_TIME DATE default TO_DATE(TO_CHAR(SYS_EXTRACT_UTC(SYSTIMESTAMP), 'yyyy-mm-dd hh24:mi:ss'), 'yyyy-mm-dd hh24:mi:ss')
)
/

comment on table OVERSEA_DELIVERY_NOTE_DTL is '海外仓交货单明细表'
/

comment on column OVERSEA_DELIVERY_NOTE_DTL.AUTO_ID_DELIVERY_NOTE is '交货单明细自增ID'
/

comment on column OVERSEA_DELIVERY_NOTE_DTL.SAP_DELIVERY_NOTE is '交货单号'
/

comment on column OVERSEA_DELIVERY_NOTE_DTL.SAP_DELIVERY_NOTE_ITEM is '交货单行号'
/

comment on column OVERSEA_DELIVERY_NOTE_DTL.MATERIAL_NO is '物料编号'
/

comment on column OVERSEA_DELIVERY_NOTE_DTL.BASIC_UNIT is '基本单位'
/

comment on column OVERSEA_DELIVERY_NOTE_DTL.UNIT_SALES is '销售单位'
/

comment on column OVERSEA_DELIVERY_NOTE_DTL.RECEIVABLE_QTY_BASIC_UNIT is '基本单位发货数量/海外应收基本单位数量'
/

comment on column OVERSEA_DELIVERY_NOTE_DTL.RECEIVABLE_QTY_UNIT_SALES is '销售单位发出的数量/海外应收销售单位数量'
/

comment on column OVERSEA_DELIVERY_NOTE_DTL.ACTUAL_QTY_BASIC_UNIT is '实收基本单位数量'
/

comment on column OVERSEA_DELIVERY_NOTE_DTL.ACTUAL_QTY_UNIT_SALES is '实收销售单位数量'
/

comment on column OVERSEA_DELIVERY_NOTE_DTL.PURCHASE_NO is '参考单据的单据编号/公司间采购订单号'
/

comment on column OVERSEA_DELIVERY_NOTE_DTL.PURCHASE_NO_ITEM is '参考项目的项目号/公司间采购订单行项目'
/

comment on column OVERSEA_DELIVERY_NOTE_DTL.REMARKS is 'SPMS备注信息'
/

comment on column OVERSEA_DELIVERY_NOTE_DTL.WERKS_DELIVERY is '发货工厂'
/

comment on column OVERSEA_DELIVERY_NOTE_DTL.WHS_LOCATION_CODE_DELIVERY is '发货库存地点'
/

comment on column OVERSEA_DELIVERY_NOTE_DTL.WERKS_RECEIVE is '收货工厂'
/

comment on column OVERSEA_DELIVERY_NOTE_DTL.WHS_LOCATION_CODE_RECEIVE is '收货库存地点'
/

comment on column OVERSEA_DELIVERY_NOTE_DTL.OVERCHARGED_QTY_BASIC_UNIT is '多收基本单位数量'
/

comment on column OVERSEA_DELIVERY_NOTE_DTL.OVERCHARGED_QTY_UNIT_SALES is '多收销售单位数量'
/

comment on column OVERSEA_DELIVERY_NOTE_DTL.UNDERCHARGED_QTY_BASIC_UNIT is '少收基本单位数量'
/

comment on column OVERSEA_DELIVERY_NOTE_DTL.UNDERCHARGED_QTY_UNIT_SALES is '少收销售单位数量'
/

comment on column OVERSEA_DELIVERY_NOTE_DTL.MOVEMENT_TYPE is '移动类型（库存管理）'
/

comment on column OVERSEA_DELIVERY_NOTE_DTL.SAP_REMARK is 'SAP交货单行备注'
/

comment on column OVERSEA_DELIVERY_NOTE_DTL.COLOR_NO is '色号批次'
/

comment on column OVERSEA_DELIVERY_NOTE_DTL.DATA_STATUS is '数据状态(默认0能被SAP更新数据；1禁止更新数据；)'
/

comment on column OVERSEA_DELIVERY_NOTE_DTL.DATA_VERSION is '数据版本号'
/

comment on column OVERSEA_DELIVERY_NOTE_DTL.CREATE_TIME is '创建时间'
/

comment on column OVERSEA_DELIVERY_NOTE_DTL.UPDATE_TIME is '更新时间'
/

create trigger TR_OVERSEA_DELIVERY_NOTE_DTL_BEF_UPDATE
	before update
	on OVERSEA_DELIVERY_NOTE_DTL
	for each row
BEGIN
    :new.UPDATE_TIME := TO_DATE(TO_CHAR(SYS_EXTRACT_UTC(SYSTIMESTAMP), 'yyyy-mm-dd hh24:mi:ss'), 'yyyy-mm-dd hh24:mi:ss');
    :new.DATA_VERSION := DATA_VERSION.nextval;
END;
/

create table OVERSEA_PACKAGE_LIST
(
	UUID_PACKAGE_LIST VARCHAR2(32) not null
		constraint OVERSEA_PACKAGE_LIST_PK
			primary key,
	SAP_PACKAGE_LIST VARCHAR2(16),
	MERCHANDISER VARCHAR2(32),
	CABINET_MODEL VARCHAR2(16),
	FROM_PORT VARCHAR2(64),
	TO_PORT VARCHAR2(64),
	DATA_STATUS VARCHAR2(2) default '0',
	CREATE_TIME DATE default TO_DATE(TO_CHAR(SYS_EXTRACT_UTC(SYSTIMESTAMP), 'yyyy-mm-dd hh24:mi:ss'), 'yyyy-mm-dd hh24:mi:ss'),
	UPDATE_TIME DATE default TO_DATE(TO_CHAR(SYS_EXTRACT_UTC(SYSTIMESTAMP), 'yyyy-mm-dd hh24:mi:ss'), 'yyyy-mm-dd hh24:mi:ss')
)
/

comment on table OVERSEA_PACKAGE_LIST is '海外仓装箱单表'
/

comment on column OVERSEA_PACKAGE_LIST.UUID_PACKAGE_LIST is '装箱单uuid'
/

comment on column OVERSEA_PACKAGE_LIST.SAP_PACKAGE_LIST is '装箱单号'
/

comment on column OVERSEA_PACKAGE_LIST.MERCHANDISER is '跟单员'
/

comment on column OVERSEA_PACKAGE_LIST.CABINET_MODEL is '柜型'
/

comment on column OVERSEA_PACKAGE_LIST.FROM_PORT is '起运港'
/

comment on column OVERSEA_PACKAGE_LIST.TO_PORT is '目的港'
/

comment on column OVERSEA_PACKAGE_LIST.DATA_STATUS is '数据状态(默认0能被SAP更新数据；1禁止更新数据；)'
/

comment on column OVERSEA_PACKAGE_LIST.CREATE_TIME is '创建时间'
/

comment on column OVERSEA_PACKAGE_LIST.UPDATE_TIME is '更新时间'
/

create trigger TR_OVERSEA_PACKAGE_LIST_BEF_UPDATE
	before update
	on OVERSEA_PACKAGE_LIST
	for each row
BEGIN
    :new.UPDATE_TIME := TO_DATE(TO_CHAR(SYS_EXTRACT_UTC(SYSTIMESTAMP), 'yyyy-mm-dd hh24:mi:ss'), 'yyyy-mm-dd hh24:mi:ss');
END;
/

create table OVERSEA_PACKAGE_LIST_DTL
(
	AUTO_ID_PACKAGE_LIST NUMBER default "SPMS"."OVERSEA_PACKAGE_LIST_DTL_SEQ"."NEXTVAL" not null
		constraint OVERSEA_PACKAGE_LIST_TDL_PK
			primary key,
	SAP_PACKAGE_LIST VARCHAR2(16),
	SAP_PACKAGE_LIST_ITEM VARCHAR2(6),
	SAP_DELIVERY_NOTE VARCHAR2(16),
	PURCHASE_NO VARCHAR2(16),
	PURCHASE_NO_ITEM VARCHAR2(6),
	DATA_STATUS VARCHAR2(2),
	CREATE_TIME DATE default TO_DATE(TO_CHAR(SYS_EXTRACT_UTC(SYSTIMESTAMP), 'yyyy-mm-dd hh24:mi:ss'), 'yyyy-mm-dd hh24:mi:ss'),
	UPDATE_TIME DATE default TO_DATE(TO_CHAR(SYS_EXTRACT_UTC(SYSTIMESTAMP), 'yyyy-mm-dd hh24:mi:ss'), 'yyyy-mm-dd hh24:mi:ss')
)
/

comment on table OVERSEA_PACKAGE_LIST_DTL is '海外装箱单明细表'
/

comment on column OVERSEA_PACKAGE_LIST_DTL.AUTO_ID_PACKAGE_LIST is '装箱单明细自增ID'
/

comment on column OVERSEA_PACKAGE_LIST_DTL.SAP_PACKAGE_LIST is '装箱单号'
/

comment on column OVERSEA_PACKAGE_LIST_DTL.SAP_PACKAGE_LIST_ITEM is '装箱单行号'
/

comment on column OVERSEA_PACKAGE_LIST_DTL.SAP_DELIVERY_NOTE is '交货单号'
/

comment on column OVERSEA_PACKAGE_LIST_DTL.PURCHASE_NO is '公司间采购订单号'
/

comment on column OVERSEA_PACKAGE_LIST_DTL.PURCHASE_NO_ITEM is '公司间采购订单行项目'
/

comment on column OVERSEA_PACKAGE_LIST_DTL.DATA_STATUS is '数据状态(默认0能被SAP更新数据；1禁止更新数据；)'
/

comment on column OVERSEA_PACKAGE_LIST_DTL.CREATE_TIME is '创建时间'
/

comment on column OVERSEA_PACKAGE_LIST_DTL.UPDATE_TIME is '更新时间'
/

create trigger TR_OVERSEA_PACKAGE_LIST_DTL_BEF_UPDATE
	before update
	on OVERSEA_PACKAGE_LIST_DTL
	for each row
BEGIN
    :new.UPDATE_TIME := TO_DATE(TO_CHAR(SYS_EXTRACT_UTC(SYSTIMESTAMP), 'yyyy-mm-dd hh24:mi:ss'), 'yyyy-mm-dd hh24:mi:ss');
END;
/

create table OVERSEA_WHS_DUMP
(
	WHS_DUMP_UUID VARCHAR2(32) not null
		constraint OVERSEA_WHS_DUMP_PK
			primary key,
	WHS_DUMP_NO VARCHAR2(32),
	WERKS_DUMP_IN VARCHAR2(6),
	WHS_LOCATION_CODE_DUMP_IN VARCHAR2(6),
	WERKS_DUMP_OUT VARCHAR2(6),
	WHS_LOCATION_CODE_DUMP_OUT VARCHAR2(6),
	CREATE_DATE VARCHAR2(12),
	SPMS_STATUS VARCHAR2(2),
	OA_WORKFLOW VARCHAR2(16),
	OA_BIANHAO VARCHAR2(32),
	OA_REMARK VARCHAR2(255),
	SUBMIT_OA_DATE VARCHAR2(12),
	OA_APPROVAL_DATE VARCHAR2(12),
	OA_FIELD VARCHAR2(16),
	DUMP_OUT_REMARK VARCHAR2(255),
	DUMP_OUT_DATE VARCHAR2(12),
	SAP_PURCHASE_NO VARCHAR2(32),
	PURCHASE_POSTING_DATE VARCHAR2(16),
	PURCHASE_VOUCHER_YEAR VARCHAR2(8),
	SAP_DELIVERY_NOTE VARCHAR2(32),
	POSTING_DATE VARCHAR2(12),
	SAP_VOUCHER_YEAR VARCHAR2(8),
	CANCEL_VOUCHER_NUMBER VARCHAR2(32),
	CANCEL_POSTING_DATE VARCHAR2(12),
	CANCEL_VOUCHER_YEAR VARCHAR2(8),
	BUSINESS_TYPE VARCHAR2(16),
	VOUCHER_TYPE VARCHAR2(16),
	PURCHASE_GROUP VARCHAR2(16),
	PURCHASE_ORG VARCHAR2(16),
	RETURN_MARK VARCHAR2(16),
	DELIVERY_DATE VARCHAR2(16),
	DELIVERY_TYPE VARCHAR2(12),
	MOVEMENT_TYPE VARCHAR2(8),
	COST_CENTER VARCHAR2(8),
	COMMENTS VARCHAR2(32),
	CREATE_USERID VARCHAR2(12),
	DATA_VERSION NUMBER default "SPMS"."DATA_VERSION"."NEXTVAL",
	CREATE_TIME DATE default TO_DATE(TO_CHAR(SYS_EXTRACT_UTC(SYSTIMESTAMP), 'yyyy-mm-dd hh24:mi:ss'), 'yyyy-mm-dd hh24:mi:ss'),
	UPDATE_TIME DATE default TO_DATE(TO_CHAR(SYS_EXTRACT_UTC(SYSTIMESTAMP), 'yyyy-mm-dd hh24:mi:ss'), 'yyyy-mm-dd hh24:mi:ss')
)
/

comment on table OVERSEA_WHS_DUMP is '海外仓转储任务表'
/

comment on column OVERSEA_WHS_DUMP.WHS_DUMP_UUID is '转储任务UUID'
/

comment on column OVERSEA_WHS_DUMP.WHS_DUMP_NO is '转储任务单号'
/

comment on column OVERSEA_WHS_DUMP.WERKS_DUMP_IN is '转入工厂代码'
/

comment on column OVERSEA_WHS_DUMP.WHS_LOCATION_CODE_DUMP_IN is '转入仓库代码'
/

comment on column OVERSEA_WHS_DUMP.WERKS_DUMP_OUT is '转出工厂代码'
/

comment on column OVERSEA_WHS_DUMP.WHS_LOCATION_CODE_DUMP_OUT is '转出仓库代码'
/

comment on column OVERSEA_WHS_DUMP.CREATE_DATE is '创建日期'
/

comment on column OVERSEA_WHS_DUMP.SPMS_STATUS is '海外SPMS转储单据状态:0删除；1草稿；2待审批；3SPMS审批通过(提交OA);4OA驳回(操作同1);5OA审批通过(出库中);6出库完成;7提交SAP获取PO失败;8提交SAP获取PO;9提交SAP获取DN失败;10提交SAP获取DN成功(扣转出方库存);11转出已撤销(撤到状态5出库中，SAP撤回成功则加回库存);'
/

comment on column OVERSEA_WHS_DUMP.OA_WORKFLOW is 'OA流程编号'
/

comment on column OVERSEA_WHS_DUMP.OA_BIANHAO is 'OA单据编号'
/

comment on column OVERSEA_WHS_DUMP.OA_REMARK is '提交OA审批备注'
/

comment on column OVERSEA_WHS_DUMP.SUBMIT_OA_DATE is '提交OA日期'
/

comment on column OVERSEA_WHS_DUMP.OA_APPROVAL_DATE is 'OA审批通过日期'
/

comment on column OVERSEA_WHS_DUMP.OA_FIELD is 'OA预留字段'
/

comment on column OVERSEA_WHS_DUMP.DUMP_OUT_REMARK is '转出备注信息'
/

comment on column OVERSEA_WHS_DUMP.DUMP_OUT_DATE is '转出完成日期'
/

comment on column OVERSEA_WHS_DUMP.SAP_PURCHASE_NO is 'SAP采购凭证号'
/

comment on column OVERSEA_WHS_DUMP.PURCHASE_POSTING_DATE is '获取采购凭证号过账日期'
/

comment on column OVERSEA_WHS_DUMP.PURCHASE_VOUCHER_YEAR is '采购凭证年份'
/

comment on column OVERSEA_WHS_DUMP.SAP_DELIVERY_NOTE is 'SAP交货单号'
/

comment on column OVERSEA_WHS_DUMP.POSTING_DATE is '获取交货单号采购凭证过账日期'
/

comment on column OVERSEA_WHS_DUMP.SAP_VOUCHER_YEAR is '交货单凭证年份'
/

comment on column OVERSEA_WHS_DUMP.CANCEL_VOUCHER_NUMBER is '撤销SAP凭证号'
/

comment on column OVERSEA_WHS_DUMP.CANCEL_POSTING_DATE is '撤销SAP过账日期'
/

comment on column OVERSEA_WHS_DUMP.CANCEL_VOUCHER_YEAR is '撤销SAP凭证年份'
/

comment on column OVERSEA_WHS_DUMP.BUSINESS_TYPE is '业务标识'
/

comment on column OVERSEA_WHS_DUMP.VOUCHER_TYPE is '凭证类型/订单类型(采购)公司内ZU01/公司间ZC01'
/

comment on column OVERSEA_WHS_DUMP.PURCHASE_GROUP is '采购组-240'
/

comment on column OVERSEA_WHS_DUMP.PURCHASE_ORG is '采购组织-1000'
/

comment on column OVERSEA_WHS_DUMP.RETURN_MARK is '退货标识'
/

comment on column OVERSEA_WHS_DUMP.DELIVERY_DATE is '项目交货日期/需求到货日期'
/

comment on column OVERSEA_WHS_DUMP.DELIVERY_TYPE is '交货类型'
/

comment on column OVERSEA_WHS_DUMP.MOVEMENT_TYPE is '移动类型'
/

comment on column OVERSEA_WHS_DUMP.COST_CENTER is '成本中心'
/

comment on column OVERSEA_WHS_DUMP.COMMENTS is '预留字段'
/

comment on column OVERSEA_WHS_DUMP.CREATE_USERID is '创建人工号'
/

comment on column OVERSEA_WHS_DUMP.DATA_VERSION is '数据版本号'
/

comment on column OVERSEA_WHS_DUMP.CREATE_TIME is '创建时间'
/

comment on column OVERSEA_WHS_DUMP.UPDATE_TIME is '更新时间'
/

create trigger TR_OVERSEA_WHS_DUMP_BEF_UPDATE
	before update
	on OVERSEA_WHS_DUMP
	for each row
BEGIN
    :new.UPDATE_TIME := TO_DATE(TO_CHAR(SYS_EXTRACT_UTC(SYSTIMESTAMP), 'yyyy-mm-dd hh24:mi:ss'), 'yyyy-mm-dd hh24:mi:ss');
    :new.DATA_VERSION := DATA_VERSION.nextval;
END;
/

create trigger TR_OVERSEA_WHS_DUMP_INSERT
	before insert
	on OVERSEA_WHS_DUMP
	for each row
DECLARE
    V_NUM1 NUMBER := 0;
BEGIN
    SELECT NVL(TO_NUMBER(SUBSTR(MAX(OWD.WHS_DUMP_NO), -3, 3)), 0) INTO V_NUM1 FROM OVERSEA_WHS_DUMP OWD
    WHERE OWD.CREATE_DATE = :NEW.CREATE_DATE
      AND OWD.WERKS_DUMP_OUT = :NEW.WERKS_DUMP_OUT
      AND OWD.WHS_LOCATION_CODE_DUMP_OUT = :NEW.WHS_LOCATION_CODE_DUMP_OUT;
    :NEW.WHS_DUMP_NO := :NEW.WERKS_DUMP_OUT||'-'||:NEW.WHS_LOCATION_CODE_DUMP_OUT||'-'||REPLACE(:NEW.CREATE_DATE, '-')||'-'||SUBSTR(1001 + V_NUM1, 2, 4);
END;
/

create table OVERSEA_WHS_DUMP_DTL
(
	AUTO_ID_DUMP_DTL NUMBER default "SPMS"."OVERSEA_WHS_DUMP_DTL_SEQ"."NEXTVAL" not null
		constraint OVERSEA_WHS_DUMP_DTL_PK
			primary key,
	WHS_DUMP_UUID VARCHAR2(32),
	ITEM NUMBER,
	MATERIAL_NO VARCHAR2(32),
	BASIC_UNIT VARCHAR2(6),
	UNIT_SALES VARCHAR2(6),
	APPLY_QTY_BASIC_UNIT NUMBER,
	APPLY_QTY_UNIT_SALES NUMBER,
	APPLY_REMARK VARCHAR2(255),
	ACTUAL_QTY_BASIC_UNIT NUMBER,
	ACTUAL_QTY_UNIT_SALES NUMBER,
	ACTUAL_STORAGE_INFO VARCHAR2(2000),
	ACTUAL_REMARK VARCHAR2(255)
)
/

comment on table OVERSEA_WHS_DUMP_DTL is '海外转储任务明细表'
/

comment on column OVERSEA_WHS_DUMP_DTL.AUTO_ID_DUMP_DTL is '海外转储明细自增ID'
/

comment on column OVERSEA_WHS_DUMP_DTL.WHS_DUMP_UUID is '转储任务UUID'
/

comment on column OVERSEA_WHS_DUMP_DTL.ITEM is '转储任务行号'
/

comment on column OVERSEA_WHS_DUMP_DTL.MATERIAL_NO is '物料编号'
/

comment on column OVERSEA_WHS_DUMP_DTL.BASIC_UNIT is '基本单位'
/

comment on column OVERSEA_WHS_DUMP_DTL.UNIT_SALES is '销售单位'
/

comment on column OVERSEA_WHS_DUMP_DTL.APPLY_QTY_BASIC_UNIT is '申请转出基本单位数量'
/

comment on column OVERSEA_WHS_DUMP_DTL.APPLY_QTY_UNIT_SALES is '申请转出销售单位数量'
/

comment on column OVERSEA_WHS_DUMP_DTL.APPLY_REMARK is '申请行备注信息'
/

comment on column OVERSEA_WHS_DUMP_DTL.ACTUAL_QTY_BASIC_UNIT is '实际转出基本单位数量'
/

comment on column OVERSEA_WHS_DUMP_DTL.ACTUAL_QTY_UNIT_SALES is '实际转出销售单位数量'
/

comment on column OVERSEA_WHS_DUMP_DTL.ACTUAL_STORAGE_INFO is '实际转出储位信息'
/

comment on column OVERSEA_WHS_DUMP_DTL.ACTUAL_REMARK is '实际转出行备注信息'
/

create table OVERSEA_PACKAGE_LIST_BOX
(
	AUTO_ID_PACKAGE_BOX NUMBER default "SPMS"."OVERSEA_PACKAGE_LIST_BOX_SEQ"."NEXTVAL" not null,
	SAP_PACKAGE_LIST VARCHAR2(16),
	SAP_PACKAGE_LIST_ITEM VARCHAR2(6),
	BOX_NOTE VARCHAR2(50),
	BOX_NOTE_ITEM VARCHAR2(6),
	BOX_STATUS VARCHAR2(2) default '0',
	DATA_STATUS VARCHAR2(2) default '0',
	ARRIVAL_DATE VARCHAR2(16),
	UPDATE_USERID VARCHAR2(16),
	CREATE_TIME DATE default TO_DATE(TO_CHAR(SYS_EXTRACT_UTC(SYSTIMESTAMP), 'yyyy-mm-dd hh24:mi:ss'), 'yyyy-mm-dd hh24:mi:ss'),
	UPDATE_TIME DATE default TO_DATE(TO_CHAR(SYS_EXTRACT_UTC(SYSTIMESTAMP), 'yyyy-mm-dd hh24:mi:ss'), 'yyyy-mm-dd hh24:mi:ss'),
	WERKS_RECEIVE VARCHAR2(6),
	WHS_LOCATION_CODE_RECEIVE VARCHAR2(6),
	MATERIAL_NO VARCHAR2(32),
	RECEIVABLE_QTY_BASIC_UNIT NUMBER default 0,
	ACTUAL_QTY_BASIC_UNIT NUMBER default 0,
	BASIC_UNIT VARCHAR2(6),
	DEMAND_NO VARCHAR2(32),
	DEMAND_ITEM VARCHAR2(6),
	DEMANDER VARCHAR2(128)
)
/

comment on table OVERSEA_PACKAGE_LIST_BOX is '海外仓收货装箱单箱码关系表'
/

comment on column OVERSEA_PACKAGE_LIST_BOX.AUTO_ID_PACKAGE_BOX is 'SPMS唯一自增ID'
/

comment on column OVERSEA_PACKAGE_LIST_BOX.SAP_PACKAGE_LIST is '装箱单号'
/

comment on column OVERSEA_PACKAGE_LIST_BOX.SAP_PACKAGE_LIST_ITEM is '装箱单行号'
/

comment on column OVERSEA_PACKAGE_LIST_BOX.BOX_NOTE is '箱码'
/

comment on column OVERSEA_PACKAGE_LIST_BOX.BOX_NOTE_ITEM is '箱码行号(对应箱码明细表的ITEM字段)'
/

comment on column OVERSEA_PACKAGE_LIST_BOX.BOX_STATUS is '箱码状态(默认0待到货;1已到货;)'
/

comment on column OVERSEA_PACKAGE_LIST_BOX.DATA_STATUS is '数据状态(默认0能被SAP更新数据；1禁止更新数据；)'
/

comment on column OVERSEA_PACKAGE_LIST_BOX.ARRIVAL_DATE is '箱码到货日期'
/

comment on column OVERSEA_PACKAGE_LIST_BOX.UPDATE_USERID is '最后更新人(确认到货人工号)'
/

comment on column OVERSEA_PACKAGE_LIST_BOX.CREATE_TIME is '创建时间'
/

comment on column OVERSEA_PACKAGE_LIST_BOX.UPDATE_TIME is '更新时间'
/

comment on column OVERSEA_PACKAGE_LIST_BOX.WERKS_RECEIVE is '实际收货工厂'
/

comment on column OVERSEA_PACKAGE_LIST_BOX.WHS_LOCATION_CODE_RECEIVE is '实际收货仓库'
/

comment on column OVERSEA_PACKAGE_LIST_BOX.MATERIAL_NO is '物料编号'
/

comment on column OVERSEA_PACKAGE_LIST_BOX.RECEIVABLE_QTY_BASIC_UNIT is '基本单位发货数量/海外应收基本单位数量'
/

comment on column OVERSEA_PACKAGE_LIST_BOX.ACTUAL_QTY_BASIC_UNIT is '实收基本单位数量'
/

comment on column OVERSEA_PACKAGE_LIST_BOX.BASIC_UNIT is '基本单位'
/

comment on column OVERSEA_PACKAGE_LIST_BOX.DEMAND_NO is '需求来源号'
/

comment on column OVERSEA_PACKAGE_LIST_BOX.DEMAND_ITEM is '需求来源行号'
/

comment on column OVERSEA_PACKAGE_LIST_BOX.DEMANDER is '需求申请人'
/

create unique index OVERSEA_PACKAGE_LIST_BOX_AUTO_ID_PACKAGE_BOX_UINDEX
	on OVERSEA_PACKAGE_LIST_BOX (AUTO_ID_PACKAGE_BOX)
/

alter table OVERSEA_PACKAGE_LIST_BOX
	add constraint OVERSEA_PACKAGE_LIST_BOX_PK
		primary key (AUTO_ID_PACKAGE_BOX)
/

create trigger TR_OVERSEA_PACKAGE_LIST_BOX_BEF_UPDATE
	before update
	on OVERSEA_PACKAGE_LIST_BOX
	for each row
BEGIN
    :new.UPDATE_TIME := TO_DATE(TO_CHAR(SYS_EXTRACT_UTC(SYSTIMESTAMP), 'yyyy-mm-dd hh24:mi:ss'), 'yyyy-mm-dd hh24:mi:ss');
END;
/

create table OVERSEA_INV_CHECK
(
	UUID VARCHAR2(32) not null
		constraint OVERSEA_INV_CHECK_PK
			primary key
		constraint OVSYS_C0031086
			check ("UUID" IS NOT NULL),
	INVENTORY_CHECK_ID VARCHAR2(32),
	WERKS VARCHAR2(6),
	WHS_LOCATION_CODE VARCHAR2(6),
	INVENTORY_CHECK_TYPE VARCHAR2(2),
	INVENTORY_CHECK_CATEGORY VARCHAR2(2),
	INVENTORY_CHECK_MOLD VARCHAR2(2),
	INVENTORY_CHECK_DATE VARCHAR2(16),
	INVENTORY_CHECK_RESULT VARCHAR2(2),
	IS_REVIEW VARCHAR2(2),
	INVENTORY_REVIEW_ID VARCHAR2(32),
	INVENTORY_CHECK_SURPLUS VARCHAR2(32),
	INVENTORY_CHECK_LOSS VARCHAR2(32),
	CREATE_DATE VARCHAR2(16),
	SPMS_STATUS VARCHAR2(2),
	REQUESTER VARCHAR2(32),
	OPERATOR VARCHAR2(32),
	REVIEWER VARCHAR2(32),
	APPROVER VARCHAR2(32),
	CREATE_TIME DATE default TO_DATE(TO_CHAR(SYS_EXTRACT_UTC(SYSTIMESTAMP), 'yyyy-mm-dd hh24:mi:ss'), 'yyyy-mm-dd hh24:mi:ss'),
	UPDATE_USERNAME VARCHAR2(64),
	UPDATE_TIME DATE
)
/

comment on table OVERSEA_INV_CHECK is '海外仓库盘点任务表'
/

comment on column OVERSEA_INV_CHECK.UUID is '海外仓库盘点任务UUID'
/

comment on column OVERSEA_INV_CHECK.INVENTORY_CHECK_ID is '仓库盘点任务单号'
/

comment on column OVERSEA_INV_CHECK.WERKS is '工厂编号'
/

comment on column OVERSEA_INV_CHECK.WHS_LOCATION_CODE is '仓库地点代码'
/

comment on column OVERSEA_INV_CHECK.INVENTORY_CHECK_TYPE is '是否杂货 X-备件货 M-贸易货'
/

comment on column OVERSEA_INV_CHECK.INVENTORY_CHECK_CATEGORY is '盘点类别 1-日常盘点 2-月度盘点 3-动态盘点 4-其它盘点'
/

comment on column OVERSEA_INV_CHECK.INVENTORY_CHECK_MOLD is '盘点类型 1-明盘 2-暗盘'
/

comment on column OVERSEA_INV_CHECK.INVENTORY_CHECK_DATE is '盘点日期'
/

comment on column OVERSEA_INV_CHECK.INVENTORY_CHECK_RESULT is '盘点结果 1-账实相符 2-盘盈 3-盘亏 4-盘盈盘亏'
/

comment on column OVERSEA_INV_CHECK.IS_REVIEW is '是否复盘 1-初盘 2-复盘'
/

comment on column OVERSEA_INV_CHECK.INVENTORY_REVIEW_ID is '复盘单据UUID'
/

comment on column OVERSEA_INV_CHECK.INVENTORY_CHECK_SURPLUS is '盘盈单据号'
/

comment on column OVERSEA_INV_CHECK.INVENTORY_CHECK_LOSS is '盘亏单据号'
/

comment on column OVERSEA_INV_CHECK.CREATE_DATE is '创建日期'
/

comment on column OVERSEA_INV_CHECK.SPMS_STATUS is '任务状态：1-待盘点；2-盘点中；3-复盘中；4-处理中；5-关闭(完成)'
/

comment on column OVERSEA_INV_CHECK.REQUESTER is '创建人工号'
/

comment on column OVERSEA_INV_CHECK.OPERATOR is '申请人'
/

comment on column OVERSEA_INV_CHECK.REVIEWER is '复核人'
/

comment on column OVERSEA_INV_CHECK.APPROVER is '审批人'
/

comment on column OVERSEA_INV_CHECK.CREATE_TIME is '创建时间'
/

comment on column OVERSEA_INV_CHECK.UPDATE_USERNAME is '更新人姓名'
/

comment on column OVERSEA_INV_CHECK.UPDATE_TIME is '更新时间'
/

create trigger TR_OVERSEA_INV_CHECK_BEF
	before update
	on OVERSEA_INV_CHECK
	for each row
BEGIN
:new.UPDATE_TIME := TO_DATE(TO_CHAR(SYS_EXTRACT_UTC(SYSTIMESTAMP), 'yyyy-mm-dd hh24:mi:ss'), 'yyyy-mm-dd hh24:mi:ss');
END;
/

create trigger TR_OVERSEA_INV_CHECK_BEF1
	before insert
	on OVERSEA_INV_CHECK
	for each row
DECLARE
    V_NUM1 NUMBER := 0;
BEGIN
    SELECT NVL(TO_NUMBER(SUBSTR(MAX(SOT.INVENTORY_CHECK_ID), -3, 3)), 0) INTO V_NUM1 FROM OVERSEA_INV_CHECK SOT
    WHERE SOT.CREATE_DATE = :NEW.CREATE_DATE
      AND SOT.WERKS = :NEW.WERKS
      AND SOT.WHS_LOCATION_CODE = :NEW.WHS_LOCATION_CODE;
    :NEW.INVENTORY_CHECK_ID := :NEW.WERKS||'-'||:NEW.WHS_LOCATION_CODE||'-'||REPLACE(:NEW.CREATE_DATE, '-')||'-'||SUBSTR(1001 + V_NUM1, 2, 4);
END;
/

create table OVERSEA_INV_CHECK_DTL
(
	UUID VARCHAR2(32),
	TASK_ROW_ID VARCHAR2(6),
	MATERIAL_NO VARCHAR2(32),
	BOX_NOTE VARCHAR2(50),
	INVENTORY_QUANTITY NUMBER,
	CHECK_QUANTITY NUMBER,
	DISCREPANCY NUMBER,
	BASIC_UNIT VARCHAR2(32),
	STORAGE_UUID VARCHAR2(32),
	REMARKS VARCHAR2(256)
)
/

comment on table OVERSEA_INV_CHECK_DTL is '盘点任务表明细表'
/

comment on column OVERSEA_INV_CHECK_DTL.UUID is '盘点任务UUID'
/

comment on column OVERSEA_INV_CHECK_DTL.TASK_ROW_ID is '盘点任务行号'
/

comment on column OVERSEA_INV_CHECK_DTL.MATERIAL_NO is '物料编号'
/

comment on column OVERSEA_INV_CHECK_DTL.BOX_NOTE is '箱码-仅用于自管仓备件管理'
/

comment on column OVERSEA_INV_CHECK_DTL.INVENTORY_QUANTITY is '库存数量'
/

comment on column OVERSEA_INV_CHECK_DTL.CHECK_QUANTITY is '实盘数量'
/

comment on column OVERSEA_INV_CHECK_DTL.DISCREPANCY is '差异数量'
/

comment on column OVERSEA_INV_CHECK_DTL.BASIC_UNIT is '基本单位'
/

comment on column OVERSEA_INV_CHECK_DTL.STORAGE_UUID is '储位UUID'
/

comment on column OVERSEA_INV_CHECK_DTL.REMARKS is '备注'
/

create table OVERSEA_INV_CHECK_ACTION
(
	UUID VARCHAR2(32) not null
		constraint OVERSEA_INV_CHECK_ACTION_PK
			primary key
		constraint OVSYS_C0031088
			check ("UUID" IS NOT NULL),
	SPMS_ID VARCHAR2(32),
	INVENTORY_ACTION_TYPE VARCHAR2(6),
	WERKS VARCHAR2(6),
	WHS_LOCATION_CODE VARCHAR2(6),
	INVENTORY_CHECK_ID VARCHAR2(32),
	INVENTORY_CHECK_RESULT VARCHAR2(2),
	INVENTORY_CHECK_TYPE VARCHAR2(2),
	INVENTORY_CHECK_CATEGORY VARCHAR2(2),
	INVENTORY_CHECK_MOLD VARCHAR2(2),
	CREATE_DATE VARCHAR2(16),
	SPMS_STATUS VARCHAR2(2),
	REQUESTER VARCHAR2(32),
	OPERATOR VARCHAR2(32),
	REVIEWER VARCHAR2(32),
	APPROVER VARCHAR2(32),
	CREATE_TIME DATE default TO_DATE(TO_CHAR(SYS_EXTRACT_UTC(SYSTIMESTAMP), 'yyyy-mm-dd hh24:mi:ss'), 'yyyy-mm-dd hh24:mi:ss'),
	UPDATE_USERNAME VARCHAR2(64),
	UPDATE_TIME DATE,
	JUSTIFICATION VARCHAR2(255),
	REMARKS VARCHAR2(255),
	POSTING_DATE VARCHAR2(16),
	COST_CENTER VARCHAR2(16),
	MOVE_TYPE VARCHAR2(6),
	VOUCHER_TEXT VARCHAR2(255),
	SAP_VOUCHER_NUMBER VARCHAR2(16),
	SAP_VOUCHER_YEAR VARCHAR2(4),
	OA_WORKFLOW VARCHAR2(16),
	OA_BIANHAO VARCHAR2(32),
	OA_REMARK VARCHAR2(255),
	SUBMIT_OA_DATE VARCHAR2(12),
	OA_APPROVAL_DATE VARCHAR2(12),
	OA_FIELD VARCHAR2(32),
	BUSINESS_TYPE VARCHAR2(16)
)
/

comment on table OVERSEA_INV_CHECK_ACTION is '仓库盘盈盘亏'
/

comment on column OVERSEA_INV_CHECK_ACTION.UUID is '仓库盘盈盘亏任务UUID'
/

comment on column OVERSEA_INV_CHECK_ACTION.SPMS_ID is '仓库盘盈盘亏单号'
/

comment on column OVERSEA_INV_CHECK_ACTION.INVENTORY_ACTION_TYPE is '盘盈盘亏类别 701-盘盈 702-盘亏'
/

comment on column OVERSEA_INV_CHECK_ACTION.WERKS is '工厂编号'
/

comment on column OVERSEA_INV_CHECK_ACTION.WHS_LOCATION_CODE is '仓库地点代码'
/

comment on column OVERSEA_INV_CHECK_ACTION.INVENTORY_CHECK_ID is '盘点单据号'
/

comment on column OVERSEA_INV_CHECK_ACTION.INVENTORY_CHECK_RESULT is '盘点结果 1-账实相符 2-盘盈 3-盘亏 4-盘盈盘亏'
/

comment on column OVERSEA_INV_CHECK_ACTION.INVENTORY_CHECK_TYPE is '是否杂货 X-备件货 M-贸易货'
/

comment on column OVERSEA_INV_CHECK_ACTION.INVENTORY_CHECK_CATEGORY is '盘点类别 1-日常盘点 2-月度盘点 3-动态盘点 4-其它盘点'
/

comment on column OVERSEA_INV_CHECK_ACTION.INVENTORY_CHECK_MOLD is '盘点类型 1-明盘 2-暗盘'
/

comment on column OVERSEA_INV_CHECK_ACTION.CREATE_DATE is '创建日期'
/

comment on column OVERSEA_INV_CHECK_ACTION.SPMS_STATUS is '任务状态：1-草稿;2-提交OA成功；3-提交OA失败；4-OA审批不通过；5-OA审批通过待上传SAP; 6-上传SAP失败 7 -上传SAP成功 8 - 已撤销'
/

comment on column OVERSEA_INV_CHECK_ACTION.REQUESTER is '创建人工号'
/

comment on column OVERSEA_INV_CHECK_ACTION.OPERATOR is '申请人'
/

comment on column OVERSEA_INV_CHECK_ACTION.REVIEWER is '复核人'
/

comment on column OVERSEA_INV_CHECK_ACTION.APPROVER is '审批人'
/

comment on column OVERSEA_INV_CHECK_ACTION.CREATE_TIME is '创建时间'
/

comment on column OVERSEA_INV_CHECK_ACTION.UPDATE_USERNAME is '更新人姓名'
/

comment on column OVERSEA_INV_CHECK_ACTION.UPDATE_TIME is '更新时间'
/

comment on column OVERSEA_INV_CHECK_ACTION.JUSTIFICATION is '申请原因'
/

comment on column OVERSEA_INV_CHECK_ACTION.REMARKS is '备注'
/

comment on column OVERSEA_INV_CHECK_ACTION.POSTING_DATE is '凭证中的过账日期-用户选'
/

comment on column OVERSEA_INV_CHECK_ACTION.COST_CENTER is '成本中心'
/

comment on column OVERSEA_INV_CHECK_ACTION.MOVE_TYPE is '移动类型'
/

comment on column OVERSEA_INV_CHECK_ACTION.VOUCHER_TEXT is '凭证抬头文本'
/

comment on column OVERSEA_INV_CHECK_ACTION.SAP_VOUCHER_NUMBER is 'SAP物料凭证-提交SAP成功后回写'
/

comment on column OVERSEA_INV_CHECK_ACTION.SAP_VOUCHER_YEAR is '会计年份/SAP凭证年份'
/

comment on column OVERSEA_INV_CHECK_ACTION.OA_WORKFLOW is 'OA流程编号'
/

comment on column OVERSEA_INV_CHECK_ACTION.OA_BIANHAO is 'OA单据编号'
/

comment on column OVERSEA_INV_CHECK_ACTION.OA_REMARK is '提交OA审批备注'
/

comment on column OVERSEA_INV_CHECK_ACTION.SUBMIT_OA_DATE is '提交OA日期'
/

comment on column OVERSEA_INV_CHECK_ACTION.OA_APPROVAL_DATE is 'OA审批通过日期'
/

comment on column OVERSEA_INV_CHECK_ACTION.OA_FIELD is 'OA预留字段'
/

comment on column OVERSEA_INV_CHECK_ACTION.BUSINESS_TYPE is '1-工厂请购单 2-海外收货损益审批 3 海外盘点盘盈盘亏审批 4 海外转储审批'
/

create trigger TR_OVERSEA_INV_CHECK_ACTION_BEF
	before update
	on OVERSEA_INV_CHECK_ACTION
	for each row
BEGIN
    :new.UPDATE_TIME := TO_DATE(TO_CHAR(SYS_EXTRACT_UTC(SYSTIMESTAMP), 'yyyy-mm-dd hh24:mi:ss'), 'yyyy-mm-dd hh24:mi:ss');
END;
/

create trigger TR_OVERSEA_INV_CHECK_ACTION_ID
	before insert
	on OVERSEA_INV_CHECK_ACTION
	for each row
DECLARE
    V_NUM1 NUMBER := 0;
BEGIN
    SELECT NVL(TO_NUMBER(SUBSTR(MAX(SOT.SPMS_ID), -3, 3)), 0) INTO V_NUM1 FROM OVERSEA_INV_CHECK_ACTION SOT
    WHERE SOT.CREATE_DATE = :NEW.CREATE_DATE
      AND SOT.WERKS = :NEW.WERKS
      AND SOT.WHS_LOCATION_CODE = :NEW.WHS_LOCATION_CODE;
    :NEW.SPMS_ID := :NEW.WERKS||'-'||:NEW.WHS_LOCATION_CODE||'-'||REPLACE(:NEW.CREATE_DATE, '-')||'-'||SUBSTR(1001 + V_NUM1, 2, 4);
END;
/

create table OVERSEA_INV_CHECK_ACTION_DTL
(
	UUID VARCHAR2(32),
	TASK_ROW_ID VARCHAR2(6),
	MATERIAL_NO VARCHAR2(32),
	BOX_NOTE VARCHAR2(50),
	INVENTORY_QUANTITY NUMBER,
	CHECK_QUANTITY NUMBER,
	DISCREPANCY NUMBER,
	BASIC_UNIT VARCHAR2(32),
	STORAGE_UUID VARCHAR2(32),
	REMARKS VARCHAR2(256),
	RELATE_ACCOUNT VARCHAR2(16),
	VOUCHER_ITEM_TEXT VARCHAR2(255)
)
/

comment on table OVERSEA_INV_CHECK_ACTION_DTL is '盘盈盘亏明细表'
/

comment on column OVERSEA_INV_CHECK_ACTION_DTL.UUID is '盘盈盘亏UUID'
/

comment on column OVERSEA_INV_CHECK_ACTION_DTL.TASK_ROW_ID is '盘盈盘亏任务行号'
/

comment on column OVERSEA_INV_CHECK_ACTION_DTL.MATERIAL_NO is '物料编号'
/

comment on column OVERSEA_INV_CHECK_ACTION_DTL.BOX_NOTE is '箱码-仅用于自管仓备件管理'
/

comment on column OVERSEA_INV_CHECK_ACTION_DTL.INVENTORY_QUANTITY is '库存数量'
/

comment on column OVERSEA_INV_CHECK_ACTION_DTL.CHECK_QUANTITY is '实盘数量'
/

comment on column OVERSEA_INV_CHECK_ACTION_DTL.DISCREPANCY is '差异数量'
/

comment on column OVERSEA_INV_CHECK_ACTION_DTL.BASIC_UNIT is '基本单位'
/

comment on column OVERSEA_INV_CHECK_ACTION_DTL.STORAGE_UUID is '储位UUID'
/

comment on column OVERSEA_INV_CHECK_ACTION_DTL.REMARKS is '备注'
/

comment on column OVERSEA_INV_CHECK_ACTION_DTL.RELATE_ACCOUNT is '供应商或债权人的帐号'
/

comment on column OVERSEA_INV_CHECK_ACTION_DTL.VOUCHER_ITEM_TEXT is '项目文本'
/

create table OVERSEA_DN
(
	UUID_DELIVERY_NOTE VARCHAR2(32) not null
		constraint OVERSEA_DN_PK
			primary key,
	SAP_DELIVERY_NOTE VARCHAR2(16),
	SPMS_STATUS VARCHAR2(2) default '0',
	WERKS_RECEIVE VARCHAR2(8),
	WHS_LOCATION_CODE_RECEIVE VARCHAR2(6),
	SAP_CREATE_DATE VARCHAR2(16),
	DELIVERY_DATE VARCHAR2(16),
	ARRIVAL_DATE VARCHAR2(16),
	REMARKS VARCHAR2(255),
	COMMENTS VARCHAR2(255),
	OPERATE_USER VARCHAR2(64),
	POSTING_DATE VARCHAR2(16),
	SAP_VOUCHER_NUMBER VARCHAR2(32),
	SAP_VOUCHER_YEAR VARCHAR2(8),
	CANCEL_POSTING_DATE VARCHAR2(16),
	CANCEL_VOUCHER_NUMBER VARCHAR2(32),
	CANCEL_VOUCHER_YEAR VARCHAR2(8),
	UNDER_VOUCHER_NUMBER VARCHAR2(32),
	UNDER_VOUCHER_YEAR VARCHAR2(8),
	UNDER_POSTING_DATE VARCHAR2(16),
	CANCEL_UNDER_POSTING_DATE VARCHAR2(16),
	CANCEL_UNDER_VOUCHER_NUMBER VARCHAR2(32),
	CANCEL_UNDER_VOUCHER_YEAR VARCHAR2(8),
	COST_CENTER VARCHAR2(32),
	DATA_STATUS VARCHAR2(2) default '0',
	DATA_VERSION NUMBER default "SPMS"."DATA_VERSION"."NEXTVAL",
	CREATE_TIME DATE default TO_DATE(TO_CHAR(SYS_EXTRACT_UTC(SYSTIMESTAMP), 'yyyy-mm-dd hh24:mi:ss'), 'yyyy-mm-dd hh24:mi:ss'),
	UPDATE_TIME DATE default TO_DATE(TO_CHAR(SYS_EXTRACT_UTC(SYSTIMESTAMP), 'yyyy-mm-dd hh24:mi:ss'), 'yyyy-mm-dd hh24:mi:ss')
)
/

comment on table OVERSEA_DN is '海外仓交货单表(空运人带/转储/本地采购)'
/

comment on column OVERSEA_DN.UUID_DELIVERY_NOTE is '交货单号uuid'
/

comment on column OVERSEA_DN.SAP_DELIVERY_NOTE is '交货单号'
/

comment on column OVERSEA_DN.SPMS_STATUS is 'SPMS收货单据状态(默认0待到货;1入库中/审批不通过;2入库完成/待审批;3审批通过;4按应收提交SAP成功/SPMS按实收入库存;5按应收提交SAP失败;6收货少收提交SAP失败;7收货少收提交SAP成功;8提交SAP完成;9收货完成;10关闭/废单;)'
/

comment on column OVERSEA_DN.WERKS_RECEIVE is '收货工厂'
/

comment on column OVERSEA_DN.WHS_LOCATION_CODE_RECEIVE is '收货仓库地址'
/

comment on column OVERSEA_DN.SAP_CREATE_DATE is 'DN创建日期'
/

comment on column OVERSEA_DN.DELIVERY_DATE is 'SAP DN交货日期'
/

comment on column OVERSEA_DN.ARRIVAL_DATE is '到货日期(用户确认到货日期)'
/

comment on column OVERSEA_DN.REMARKS is 'SPMS备注信息'
/

comment on column OVERSEA_DN.COMMENTS is 'SPMS评论/预留'
/

comment on column OVERSEA_DN.OPERATE_USER is '操作员'
/

comment on column OVERSEA_DN.POSTING_DATE is '过账日期/按应收入库凭证'
/

comment on column OVERSEA_DN.SAP_VOUCHER_NUMBER is 'SAP凭证号/按应收入库凭证'
/

comment on column OVERSEA_DN.SAP_VOUCHER_YEAR is 'SAP凭证年份/按应收入库凭证'
/

comment on column OVERSEA_DN.CANCEL_POSTING_DATE is '撤销SAP过账日期/按应收入库凭证'
/

comment on column OVERSEA_DN.CANCEL_VOUCHER_NUMBER is '撤销SAP凭证/按应收入库凭证'
/

comment on column OVERSEA_DN.CANCEL_VOUCHER_YEAR is '撤销SAP凭证年份/按应收入库凭证'
/

comment on column OVERSEA_DN.UNDER_VOUCHER_NUMBER is 'SAP凭证号/少收凭证'
/

comment on column OVERSEA_DN.UNDER_VOUCHER_YEAR is 'SAP凭证年份/少收凭证'
/

comment on column OVERSEA_DN.UNDER_POSTING_DATE is 'SAP过账日期/少收凭证'
/

comment on column OVERSEA_DN.CANCEL_UNDER_POSTING_DATE is '撤销SAP过账日期/少收凭证'
/

comment on column OVERSEA_DN.CANCEL_UNDER_VOUCHER_NUMBER is '撤销SAP凭证/少收凭证'
/

comment on column OVERSEA_DN.CANCEL_UNDER_VOUCHER_YEAR is '撤销SAP凭证年份/少收凭证'
/

comment on column OVERSEA_DN.COST_CENTER is '成本中心'
/

comment on column OVERSEA_DN.DATA_STATUS is '数据状态(默认0能被SAP更新数据；1禁止更新数据；)'
/

comment on column OVERSEA_DN.DATA_VERSION is '数据版本号'
/

comment on column OVERSEA_DN.CREATE_TIME is '创建时间'
/

comment on column OVERSEA_DN.UPDATE_TIME is '更新时间'
/

create trigger TR_OVERSEA_DN_BEF_UPDATE
	before update
	on OVERSEA_DN
	for each row
BEGIN
    :new.UPDATE_TIME := TO_DATE(TO_CHAR(SYS_EXTRACT_UTC(SYSTIMESTAMP), 'yyyy-mm-dd hh24:mi:ss'), 'yyyy-mm-dd hh24:mi:ss');
    :new.DATA_VERSION := DATA_VERSION.nextval;
END;
/

create table OVERSEA_DN_DTL
(
	AUTO_ID_DN NUMBER default "SPMS"."OVERSEA_DN_DTL_SEQ"."NEXTVAL" not null
		constraint OVERSEA_DN_DTL_PK
			primary key,
	SAP_DELIVERY_NOTE VARCHAR2(16),
	SAP_DELIVERY_NOTE_ITEM VARCHAR2(6),
	SAP_PURCHASE_ORDER VARCHAR2(16),
	SAP_PURCHASE_ORDER_ITEM VARCHAR2(6),
	WERKS_DELIVERY VARCHAR2(6),
	WHS_LOCATION_CODE_DELIVERY VARCHAR2(6),
	WERKS_RECEIVE VARCHAR2(6),
	WHS_LOCATION_CODE_RECEIVE VARCHAR2(6),
	MATERIAL_NO VARCHAR2(32),
	BASIC_UNIT VARCHAR2(6),
	UNIT_SALES VARCHAR2(6),
	RECEIVABLE_QTY_BASIC_UNIT NUMBER default 0,
	RECEIVABLE_QTY_UNIT_SALES NUMBER default 0,
	ACTUAL_QTY_BASIC_UNIT NUMBER default 0,
	ACTUAL_QTY_UNIT_SALES NUMBER default 0,
	STORAGE_INFO VARCHAR2(2000),
	REMARKS VARCHAR2(255),
	COMMENTS VARCHAR2(255),
	BOX_NOTE VARCHAR2(32),
	BOX_NOTE_ITEM VARCHAR2(6),
	DOMESTIC_ACTUAL_QTY NUMBER default 0,
	DEMAND_NO VARCHAR2(32),
	DEMAND_ITEM VARCHAR2(6),
	DEMANDER VARCHAR2(64),
	DATA_STATUS VARCHAR2(2) default '0',
	DATA_VERSION NUMBER default "SPMS"."DATA_VERSION"."NEXTVAL",
	CREATE_TIME DATE default TO_DATE(TO_CHAR(SYS_EXTRACT_UTC(SYSTIMESTAMP), 'yyyy-mm-dd hh24:mi:ss'), 'yyyy-mm-dd hh24:mi:ss'),
	UPDATE_TIME DATE default TO_DATE(TO_CHAR(SYS_EXTRACT_UTC(SYSTIMESTAMP), 'yyyy-mm-dd hh24:mi:ss'), 'yyyy-mm-dd hh24:mi:ss')
)
/

comment on table OVERSEA_DN_DTL is '交货单明细采购订单明细关系表'
/

comment on column OVERSEA_DN_DTL.AUTO_ID_DN is '交货单明细表自增ID'
/

comment on column OVERSEA_DN_DTL.SAP_DELIVERY_NOTE is '交货单号'
/

comment on column OVERSEA_DN_DTL.SAP_DELIVERY_NOTE_ITEM is '交货单行号'
/

comment on column OVERSEA_DN_DTL.SAP_PURCHASE_ORDER is '参考采购凭证号PO'
/

comment on column OVERSEA_DN_DTL.SAP_PURCHASE_ORDER_ITEM is '参考采购凭证号PO行号'
/

comment on column OVERSEA_DN_DTL.WERKS_DELIVERY is '发货工厂'
/

comment on column OVERSEA_DN_DTL.WHS_LOCATION_CODE_DELIVERY is '发货仓库地址'
/

comment on column OVERSEA_DN_DTL.WERKS_RECEIVE is '收货工厂'
/

comment on column OVERSEA_DN_DTL.WHS_LOCATION_CODE_RECEIVE is '收货仓库地址'
/

comment on column OVERSEA_DN_DTL.MATERIAL_NO is '物料编号'
/

comment on column OVERSEA_DN_DTL.BASIC_UNIT is '基本单位'
/

comment on column OVERSEA_DN_DTL.UNIT_SALES is '销售单位'
/

comment on column OVERSEA_DN_DTL.RECEIVABLE_QTY_BASIC_UNIT is '交货数量/应收基本单位数量'
/

comment on column OVERSEA_DN_DTL.RECEIVABLE_QTY_UNIT_SALES is '交货数量/应收销售单位数量'
/

comment on column OVERSEA_DN_DTL.ACTUAL_QTY_BASIC_UNIT is '实收基本单位数量'
/

comment on column OVERSEA_DN_DTL.ACTUAL_QTY_UNIT_SALES is '实收销售单位数量'
/

comment on column OVERSEA_DN_DTL.STORAGE_INFO is '储位信息'
/

comment on column OVERSEA_DN_DTL.REMARKS is '行备注信息'
/

comment on column OVERSEA_DN_DTL.COMMENTS is '行评论信息/预留'
/

comment on column OVERSEA_DN_DTL.BOX_NOTE is '箱码'
/

comment on column OVERSEA_DN_DTL.BOX_NOTE_ITEM is '箱码行号(对应箱码明细表的ITEM字段)'
/

comment on column OVERSEA_DN_DTL.DOMESTIC_ACTUAL_QTY is '国内实际发货基本单位数量'
/

comment on column OVERSEA_DN_DTL.DEMAND_NO is 'SPMS需求来源号'
/

comment on column OVERSEA_DN_DTL.DEMAND_ITEM is 'SPMS需求来源行号'
/

comment on column OVERSEA_DN_DTL.DEMANDER is '需求申请人'
/

comment on column OVERSEA_DN_DTL.DATA_STATUS is '数据状态(默认0能被SAP更新数据；1禁止更新数据；)'
/

comment on column OVERSEA_DN_DTL.DATA_VERSION is '数据版本号'
/

comment on column OVERSEA_DN_DTL.CREATE_TIME is '创建时间'
/

comment on column OVERSEA_DN_DTL.UPDATE_TIME is '更新时间'
/

create trigger TR_OVERSEA_DN_DTL_BEF_UPDATE
	before update
	on OVERSEA_DN_DTL
	for each row
BEGIN
    :new.UPDATE_TIME := TO_DATE(TO_CHAR(SYS_EXTRACT_UTC(SYSTIMESTAMP), 'yyyy-mm-dd hh24:mi:ss'), 'yyyy-mm-dd hh24:mi:ss');
    :new.DATA_VERSION := DATA_VERSION.nextval;
END;
/

create table OVERSEA_PURCHASE_ORDER
(
	UUID_PURCHASE_ORDER VARCHAR2(32) not null
		constraint OVERSEA_PURCHASE_ORDER_PK
			primary key,
	SAP_PURCHASE_ORDER VARCHAR2(16),
	BUSINESS_TYPE VARCHAR2(4),
	SAP_CREATE_DATE VARCHAR2(16),
	SUPPLIER_CODE VARCHAR2(16),
	SUPPLIER_DESC VARCHAR2(255),
	WERKS_DELIVERY VARCHAR2(6),
	PURCHASE_GROUP VARCHAR2(3),
	PURCHASE_ORG VARCHAR2(4),
	CREATE_USER VARCHAR2(32),
	VOUCHER_TYPE VARCHAR2(6),
	SAP_REMARK VARCHAR2(255),
	DATA_STATUS VARCHAR2(2) default '0',
	DATA_VERSION NUMBER default "SPMS"."DATA_VERSION"."NEXTVAL",
	CREATE_TIME DATE default TO_DATE(TO_CHAR(SYS_EXTRACT_UTC(SYSTIMESTAMP), 'yyyy-mm-dd hh24:mi:ss'), 'yyyy-mm-dd hh24:mi:ss'),
	UPDATE_TIME DATE default TO_DATE(TO_CHAR(SYS_EXTRACT_UTC(SYSTIMESTAMP), 'yyyy-mm-dd hh24:mi:ss'), 'yyyy-mm-dd hh24:mi:ss')
)
/

comment on table OVERSEA_PURCHASE_ORDER is '海外仓采购订单表'
/

comment on column OVERSEA_PURCHASE_ORDER.UUID_PURCHASE_ORDER is '采购订单表uuid'
/

comment on column OVERSEA_PURCHASE_ORDER.SAP_PURCHASE_ORDER is '采购凭证号PO'
/

comment on column OVERSEA_PURCHASE_ORDER.BUSINESS_TYPE is '业务标识(01空运人带;02本地采购;03海外转储;05退货订单;)'
/

comment on column OVERSEA_PURCHASE_ORDER.SAP_CREATE_DATE is 'SAP凭证日期'
/

comment on column OVERSEA_PURCHASE_ORDER.SUPPLIER_CODE is '供应商编号'
/

comment on column OVERSEA_PURCHASE_ORDER.SUPPLIER_DESC is '供应商名称'
/

comment on column OVERSEA_PURCHASE_ORDER.WERKS_DELIVERY is '发货/转出工厂'
/

comment on column OVERSEA_PURCHASE_ORDER.PURCHASE_GROUP is '采购组(当业务标识为01、03时，满足下列数据的值才取，240海外转储，260为空运，250人带)'
/

comment on column OVERSEA_PURCHASE_ORDER.PURCHASE_ORG is '采购组织'
/

comment on column OVERSEA_PURCHASE_ORDER.CREATE_USER is '建单人'
/

comment on column OVERSEA_PURCHASE_ORDER.VOUCHER_TYPE is '凭证类型(当业务标识为02时，ZN03为本地采购订单)'
/

comment on column OVERSEA_PURCHASE_ORDER.SAP_REMARK is 'SAP表头文本'
/

comment on column OVERSEA_PURCHASE_ORDER.DATA_STATUS is '数据状态(默认0能被SAP更新数据；1禁止更新数据；)'
/

comment on column OVERSEA_PURCHASE_ORDER.DATA_VERSION is '数据版本号'
/

comment on column OVERSEA_PURCHASE_ORDER.CREATE_TIME is '创建时间'
/

comment on column OVERSEA_PURCHASE_ORDER.UPDATE_TIME is '更新时间'
/

create trigger TR_OVERSEA_PURCHASE_ORDER_BEF_UPDATE
	before update
	on OVERSEA_PURCHASE_ORDER
	for each row
BEGIN
    :new.UPDATE_TIME := TO_DATE(TO_CHAR(SYS_EXTRACT_UTC(SYSTIMESTAMP), 'yyyy-mm-dd hh24:mi:ss'), 'yyyy-mm-dd hh24:mi:ss');
    :new.DATA_VERSION := DATA_VERSION.nextval;
END;
/

create table OVERSEA_PURCHASE_ORDER_DTL
(
	AUTO_ID_PURCHASE_ORDER NUMBER default "SPMS"."OVERSEA_PURCHASE_ORDER_DTL_SEQ"."NEXTVAL" not null
		constraint OVERSEA_PURCHASE_ORDER_DTL_PK
			primary key,
	SAP_PURCHASE_ORDER VARCHAR2(16),
	SAP_PURCHASE_ORDER_ITEM VARCHAR2(6),
	DELIVERY_DATE VARCHAR2(16),
	WERKS_RECEIVE VARCHAR2(6),
	WHS_LOCATION_CODE_RECEIVE VARCHAR2(6),
	MATERIAL_NO VARCHAR2(32),
	MATERIAL_ZH_DESC VARCHAR2(255),
	BASIC_UNIT VARCHAR2(6),
	RECEIVABLE_QTY_BASIC_UNIT NUMBER default 0,
	RETURN_MARK VARCHAR2(2),
	DATA_STATUS VARCHAR2(2) default '0',
	DATA_VERSION NUMBER default "SPMS"."DATA_VERSION"."NEXTVAL",
	CREATE_TIME DATE default TO_DATE(TO_CHAR(SYS_EXTRACT_UTC(SYSTIMESTAMP), 'yyyy-mm-dd hh24:mi:ss'), 'yyyy-mm-dd hh24:mi:ss'),
	UPDATE_TIME DATE default TO_DATE(TO_CHAR(SYS_EXTRACT_UTC(SYSTIMESTAMP), 'yyyy-mm-dd hh24:mi:ss'), 'yyyy-mm-dd hh24:mi:ss')
)
/

comment on table OVERSEA_PURCHASE_ORDER_DTL is '海外仓采购订单表明细'
/

comment on column OVERSEA_PURCHASE_ORDER_DTL.AUTO_ID_PURCHASE_ORDER is '采购订单明细自增ID'
/

comment on column OVERSEA_PURCHASE_ORDER_DTL.SAP_PURCHASE_ORDER is '采购凭证号PO'
/

comment on column OVERSEA_PURCHASE_ORDER_DTL.SAP_PURCHASE_ORDER_ITEM is '采购凭证号PO行号'
/

comment on column OVERSEA_PURCHASE_ORDER_DTL.DELIVERY_DATE is 'DN交货日期'
/

comment on column OVERSEA_PURCHASE_ORDER_DTL.WERKS_RECEIVE is '收货/转入工厂'
/

comment on column OVERSEA_PURCHASE_ORDER_DTL.WHS_LOCATION_CODE_RECEIVE is '收货/转入仓库地址'
/

comment on column OVERSEA_PURCHASE_ORDER_DTL.MATERIAL_NO is '物料编号'
/

comment on column OVERSEA_PURCHASE_ORDER_DTL.MATERIAL_ZH_DESC is '物料描述'
/

comment on column OVERSEA_PURCHASE_ORDER_DTL.BASIC_UNIT is '基本单位'
/

comment on column OVERSEA_PURCHASE_ORDER_DTL.RECEIVABLE_QTY_BASIC_UNIT is '交货数量/应收基本单位数量'
/

comment on column OVERSEA_PURCHASE_ORDER_DTL.RETURN_MARK is '退货标识'
/

comment on column OVERSEA_PURCHASE_ORDER_DTL.DATA_STATUS is '数据状态(默认0能被SAP更新数据；1禁止更新数据；)'
/

comment on column OVERSEA_PURCHASE_ORDER_DTL.DATA_VERSION is '数据版本号'
/

comment on column OVERSEA_PURCHASE_ORDER_DTL.CREATE_TIME is '创建时间'
/

comment on column OVERSEA_PURCHASE_ORDER_DTL.UPDATE_TIME is '更新时间'
/

create trigger TR_OVERSEA_PURCHASE_ORDER_DTL_BEF_UPDATE
	before update
	on OVERSEA_PURCHASE_ORDER_DTL
	for each row
BEGIN
    :new.UPDATE_TIME := TO_DATE(TO_CHAR(SYS_EXTRACT_UTC(SYSTIMESTAMP), 'yyyy-mm-dd hh24:mi:ss'), 'yyyy-mm-dd hh24:mi:ss');
    :new.DATA_VERSION := DATA_VERSION.nextval;
END;
/

create view MATERIAL_SAP_LONG_CONTRACT as
SELECT sap.MATERIAL_NO,
           sap.MATERIAL_ZH_DESC,
           sap.MATERIAL_EN_DESC,
            sap.PRODUCT_LEVEL,
            sap.PRODUCT_LEVEL_LARGE,
            sap.PRODUCT_LEVEL_LARGE_DESC,
            sap.PRODUCT_LEVEL_MID,
            sap.PRODUCT_LEVEL_MID_DESC,
            sap.PRODUCT_LEVEL_SMALL,
            sap.PRODUCT_LEVEL_SMALL_DESC,
            sap.BRAND AS "SAP_BRAND",
            sap.BASIC_UNIT,
            sap.PURCHASE_UNIT,
            sap.NET_WEIGHT,
            sap.GROSS_WEIGHT,
            sap.WEIGHT_UNIT,
            sap.VOLUME_QTY,
            sap.VOLUME_UNIT,
            sap.LIGHT_HEAVY_MARK,
            sap.MATERIAL_STATUS,
            sap.SPECS,
            sap.MATERIAL_MODEL,
            sap.SUPPLY_DESC,
            sap.SUPPLIER_MATERIAL_NO,
           srm.LONG_CONTRACT_CODE,
           srm.SUPPLIER_CODE,
           srm.SUPPLIER_NAME,
           srm.BRAND AS "SRM_BRAND",
           srm.PURCHASING_CYCLE
 FROM MATERIAL_SAP sap
        LEFT JOIN MATERIAL_SRM_LONG_CONTRACT srm ON sap.MATERIAL_NO = srm.MATERIAL_NO
/

create PROCEDURE "INVENT_AGE_BETA" (materialNo IN VARCHAR2, werks IN VARCHAR2, whsCode IN VARCHAR2, average OUT INTEGER)
AS
materialTotalStorage NUMBER;
descQty NUMBER;
descQtyFinal NUMBER;
diff NUMBER;
age DECIMAL;
diffDate NUMBER;
diffjits DECIMAL;
finalAge NUMBER;
BEGIN
	-- routine body goes here, e.g.
	-- 物料在特定库房的总库存
	SELECT SUM(QUANTITY) INTO materialTotalStorage FROM (SELECT QUANTITY, WERKS, ws.UUID, WHS_LOCATION_CODE, MATERIAL_NO FROM WHS_STORAGE ws JOIN WHS_STORAGE_INVENTORY wsi ON ws.UUID = wsi.UUID WHERE WERKS = werks AND WHS_LOCATION_CODE = whsCode) WHERE MATERIAL_NO = materialNo;
	DBMS_OUTPUT.PUT_LINE('总库存'||materialTotalStorage||'');
	
------改进版本
	  SELECT DESC_QTY INTO descQtyFinal FROM (SELECT DESC_QTY, CREATE_TIME FROM (SELECT QTY,CREATE_TIME,SUM(QTY) OVER(ORDER BY CREATE_TIME DESC) DESC_QTY FROM (SELECT SUM(QUANTITY) QTY, CREATE_TIME FROM WHS_OPERATE_LOG WHERE WERKS = werks AND WHS_LOCATION_CODE = whsCode AND MATERIAL_NO = materialNo AND COMMENTS = '自管仓贸易货入储位' GROUP BY CREATE_TIME)) WHERE DESC_QTY <= materialTotalStorage ORDER BY CREATE_TIME ASC) WHERE ROWNUM = 1;
	DBMS_OUTPUT.PUT_LINE('入库累积数量小于总库存最接近单据入库数量: '||descQtyFinal||'个');
	
-- 	--计算差额
	diff := materialTotalStorage-descQtyFinal;
	DBMS_OUTPUT.PUT_LINE('库存和入库累积数量差额: '||diff||'个');

------ 查阅刚超过的那条时间
	  SELECT ROUND(TO_NUMBER(SYSDATE-CREATE_TIME)) INTO diffDate FROM (SELECT DESC_QTY, CREATE_TIME FROM (SELECT QTY,CREATE_TIME,SUM(QTY) OVER(ORDER BY CREATE_TIME DESC) DESC_QTY FROM (SELECT SUM(QUANTITY) QTY, CREATE_TIME FROM WHS_OPERATE_LOG WHERE WERKS = werks AND WHS_LOCATION_CODE = whsCode AND MATERIAL_NO = materialNo AND COMMENTS = '自管仓贸易货入储位' GROUP BY CREATE_TIME)) WHERE DESC_QTY >= materialTotalStorage ORDER BY CREATE_TIME ASC) WHERE ROWNUM = 1;
	DBMS_OUTPUT.PUT_LINE('入库累积数量大于库存单据和今天相差天数: '||diffDate||'');
	diffJits :=diffDate*diff/materialTotalStorage;
	DBMS_OUTPUT.PUT_LINE('差额部分增加库龄: '||diffJits||'');
	
-- 	--计算库龄
	 SELECT SUM(ROUND(TO_NUMBER(SYSDATE-CREATE_TIME))*QTY) INTO average FROM (SELECT DESC_QTY, CREATE_TIME, QTY FROM (SELECT QTY,CREATE_TIME,SUM(QTY) OVER(ORDER BY CREATE_TIME DESC) DESC_QTY FROM (SELECT SUM(QUANTITY) QTY, CREATE_TIME FROM WHS_OPERATE_LOG WHERE WERKS = werks AND WHS_LOCATION_CODE = whsCode AND MATERIAL_NO = materialNo AND COMMENTS = '自管仓贸易货入储位' GROUP BY CREATE_TIME) WHERE 1=1 ORDER BY CREATE_TIME ASC) WHERE DESC_QTY <= materialTotalStorage ORDER BY CREATE_TIME ASC);
	 
	 age := average/materialTotalStorage;
	 finalAge := age+diffJits;
	DBMS_OUTPUT.PUT_LINE('基础时间'||age||''); 
	DBMS_OUTPUT.PUT_LINE('库龄'||finalAge||''); 
END;
/

create PROCEDURE "INVENT_AGE_FINAL" (materialNo IN VARCHAR2, werks IN VARCHAR2, whsCode IN VARCHAR2, average OUT INTEGER)
AS
materialTotalStorage NUMBER;
descQty NUMBER;
descQtyFinal NUMBER;
diff NUMBER;
age DECIMAL;
BEGIN
	-- routine body goes here, e.g.
	-- 物料在特定库房的总库存
	SELECT SUM(QUANTITY) INTO materialTotalStorage FROM (SELECT QUANTITY, WERKS, ws.UUID, WHS_LOCATION_CODE, MATERIAL_NO FROM WHS_STORAGE ws JOIN WHS_STORAGE_INVENTORY wsi ON ws.UUID = wsi.UUID WHERE WERKS = werks AND WHS_LOCATION_CODE = whsCode) WHERE MATERIAL_NO = materialNo;
	DBMS_OUTPUT.PUT_LINE('总库存'||materialTotalStorage||'');
-- 	-- 贸易货的上架记录总数等于总库存的那条
-- -- 		SELECT DESC_QTY INTO descQty FROM (SELECT QUANTITY,CREATE_TIME,SUM(QUANTITY) OVER(ORDER BY CREATE_TIME DESC) DESC_QTY FROM WHS_OPERATE_LOG WHERE WERKS = werks AND WHS_LOCATION_CODE = whsCode AND MATERIAL_NO = materialNo AND COMMENTS = '自管仓贸易货入储位') WHERE DESC_QTY <= total ORDER BY CREATE_TIME ASC;
-- 	--找到那一条
	  SELECT DESC_QTY INTO descQtyFinal FROM (SELECT DESC_QTY, CREATE_TIME FROM (SELECT QUANTITY,CREATE_TIME,SUM(QUANTITY) OVER(ORDER BY CREATE_TIME DESC) DESC_QTY FROM WHS_OPERATE_LOG WHERE WERKS = werks AND WHS_LOCATION_CODE = whsCode AND MATERIAL_NO = materialNo AND COMMENTS = '自管仓贸易货入储位') WHERE DESC_QTY <= materialTotalStorage ORDER BY CREATE_TIME ASC) WHERE ROWNUM = 1;
	DBMS_OUTPUT.PUT_LINE('具体那条'||descQtyFinal||'');
	
	  SELECT DESC_QTY INTO descQtyFinal FROM (SELECT DESC_QTY, CREATE_TIME FROM (SELECT QUANTITY,CREATE_TIME,SUM(QUANTITY) OVER(ORDER BY CREATE_TIME DESC) DESC_QTY FROM WHS_OPERATE_LOG WHERE WERKS = werks AND WHS_LOCATION_CODE = whsCode AND MATERIAL_NO = materialNo AND COMMENTS = '自管仓贸易货入储位') WHERE DESC_QTY <= materialTotalStorage ORDER BY CREATE_TIME ASC) WHERE ROWNUM = 1;
	DBMS_OUTPUT.PUT_LINE('具体那条'||descQtyFinal||'');	
	
	
	
-- 	--计算差额
-- 	diff := descQtyFinal-materialTotalStorage;
-- 	DBMS_OUTPUT.PUT_LINE('差额'||diff||'');
-- 	--计算库龄
	 SELECT SUM(ROUND(TO_NUMBER(SYSDATE-CREATE_TIME))*QUANTITY) INTO average FROM (SELECT DESC_QTY, CREATE_TIME, QUANTITY FROM (SELECT QUANTITY,CREATE_TIME,SUM(QUANTITY) OVER(ORDER BY CREATE_TIME DESC) DESC_QTY FROM WHS_OPERATE_LOG WHERE WERKS = werks AND WHS_LOCATION_CODE = whsCode AND MATERIAL_NO = materialNo AND COMMENTS = '自管仓贸易货入储位') WHERE DESC_QTY <= materialTotalStorage ORDER BY CREATE_TIME ASC);
	 age := average/materialTotalStorage;
	DBMS_OUTPUT.PUT_LINE('库龄'||age||''); 
END;
/

create FUNCTION "CREATE_AGE" (materialNo IN VARCHAR2, werks IN VARCHAR2, whsCode IN VARCHAR2)
RETURN NUMBER-- VARCHAR2
AS
materialTotalStorage NUMBER;
descQty NUMBER;
descQtyFinal NUMBER;
diff NUMBER;
age DECIMAL;
diffDate NUMBER;
diffjits DECIMAL;
finalAge NUMBER;
average NUMBER;
BEGIN
	-- routine body goes here, e.g.
	-- DBMS_OUTPUT.PUT_LINE('Navicat for Oracle');
		-- routine body goes here, e.g.
	-- 物料在特定库房的总库存
	SELECT SUM(QUANTITY) INTO materialTotalStorage FROM (SELECT QUANTITY, WERKS, ws.UUID, WHS_LOCATION_CODE, MATERIAL_NO FROM WHS_STORAGE ws JOIN WHS_STORAGE_INVENTORY wsi ON ws.UUID = wsi.UUID WHERE WERKS = werks AND WHS_LOCATION_CODE = whsCode) WHERE MATERIAL_NO = materialNo;
	DBMS_OUTPUT.PUT_LINE('总库存'||materialTotalStorage||'');
-- 	-- 贸易货的上架记录总数等于总库存的那条
-- -- 		SELECT DESC_QTY INTO descQty FROM (SELECT QUANTITY,CREATE_TIME,SUM(QUANTITY) OVER(ORDER BY CREATE_TIME DESC) DESC_QTY FROM WHS_OPERATE_LOG WHERE WERKS = werks AND WHS_LOCATION_CODE = whsCode AND MATERIAL_NO = materialNo AND COMMENTS = '自管仓贸易货入储位') WHERE DESC_QTY <= total ORDER BY CREATE_TIME ASC;
-- 	--找到那一条
-- 	  SELECT DESC_QTY INTO descQtyFinal FROM (SELECT DESC_QTY, CREATE_TIME FROM (SELECT QUANTITY,CREATE_TIME,SUM(QUANTITY) OVER(ORDER BY CREATE_TIME DESC) DESC_QTY FROM WHS_OPERATE_LOG WHERE WERKS = werks AND WHS_LOCATION_CODE = whsCode AND MATERIAL_NO = materialNo AND COMMENTS = '自管仓贸易货入储位') WHERE DESC_QTY <= materialTotalStorage ORDER BY CREATE_TIME ASC) WHERE ROWNUM = 1;
-- 	DBMS_OUTPUT.PUT_LINE('具体那条'||descQtyFinal||'');
	
------改进版本
	  SELECT DESC_QTY INTO descQtyFinal FROM (SELECT DESC_QTY, CREATE_TIME FROM (SELECT QTY,CREATE_TIME,SUM(QTY) OVER(ORDER BY CREATE_TIME DESC) DESC_QTY FROM (SELECT SUM(QUANTITY) QTY, CREATE_TIME FROM WHS_OPERATE_LOG WHERE WERKS = werks AND WHS_LOCATION_CODE = whsCode AND MATERIAL_NO = materialNo AND COMMENTS = '自管仓贸易货入储位' GROUP BY CREATE_TIME)) WHERE DESC_QTY <= materialTotalStorage ORDER BY CREATE_TIME ASC) WHERE ROWNUM = 1;
	DBMS_OUTPUT.PUT_LINE('入库累积数量小于总库存最接近单据入库数量: '||descQtyFinal||'个');
	
-- 	--计算差额
	diff := materialTotalStorage-descQtyFinal;
	DBMS_OUTPUT.PUT_LINE('库存和入库累积数量差额: '||diff||'个');

------ 查阅刚超过的那条时间
	  SELECT ROUND(TO_NUMBER(SYSDATE-CREATE_TIME)) INTO diffDate FROM (SELECT DESC_QTY, CREATE_TIME FROM (SELECT QTY,CREATE_TIME,SUM(QTY) OVER(ORDER BY CREATE_TIME DESC) DESC_QTY FROM (SELECT SUM(QUANTITY) QTY, CREATE_TIME FROM WHS_OPERATE_LOG WHERE WERKS = werks AND WHS_LOCATION_CODE = whsCode AND MATERIAL_NO = materialNo AND COMMENTS = '自管仓贸易货入储位' GROUP BY CREATE_TIME)) WHERE DESC_QTY >= materialTotalStorage ORDER BY CREATE_TIME ASC) WHERE ROWNUM = 1;
	DBMS_OUTPUT.PUT_LINE('入库累积数量大于库存单据和今天相差天数: '||diffDate||'');
	diffJits :=diffDate*diff/materialTotalStorage;
	DBMS_OUTPUT.PUT_LINE('差额部分增加库龄: '||diffJits||'');
	
-- 	--计算库龄
	 SELECT SUM(ROUND(TO_NUMBER(SYSDATE-CREATE_TIME))*QTY) INTO average FROM (SELECT DESC_QTY, CREATE_TIME, QTY FROM (SELECT QTY,CREATE_TIME,SUM(QTY) OVER(ORDER BY CREATE_TIME DESC) DESC_QTY FROM (SELECT SUM(QUANTITY) QTY, CREATE_TIME FROM WHS_OPERATE_LOG WHERE WERKS = werks AND WHS_LOCATION_CODE = whsCode AND MATERIAL_NO = materialNo AND COMMENTS = '自管仓贸易货入储位' GROUP BY CREATE_TIME) WHERE 1=1 ORDER BY CREATE_TIME ASC) WHERE DESC_QTY <= materialTotalStorage ORDER BY CREATE_TIME ASC);
	 
	 age := average/materialTotalStorage;
	 finalAge := age+diffJits;
	DBMS_OUTPUT.PUT_LINE('基础时间'||age||''); 
	DBMS_OUTPUT.PUT_LINE('库龄'||finalAge||''); 
	RETURN finalAge;
END;
/

create PROCEDURE "HANDLE_AGE" AS
average NUMBER;
BEGIN
	-- routine body goes here, e.g.
	-- DBMS_OUTPUT.PUT_LINE('Navicat for Oracle');
	EXECUTE IMMEDIATE 'truncate table WHS_INVENTORY_AGE';
	INSERT INTO WHS_INVENTORY_AGE
	   SELECT WERKS, WHS_LOCATION_CODE, MATERIAL_NO, CREATE_AGE(MATERIAL_NO,WERKS, WHS_LOCATION_CODE), SYSDATE FROM 
		 (SELECT DISTINCT
				* 
			FROM
				(
				SELECT
					WERKS,
					WHS_LOCATION_CODE,
					wsi.MATERIAL_NO 
				FROM
					WHS_STORAGE ws
					LEFT JOIN WHS_STORAGE_INVENTORY wsi ON ws.UUID = wsi.UUID 
				WHERE
				wsi.MATERIAL_NO IS NOT NULL 
				));
END;
/

